Category Archives: Programming

CodeStock 2008

I just found out about a new developers conference put on by the East Tennessee .Net User Group.

CodeStock 2008

To quote from their website:

“CodeStock’s mission is to bring the best and brightest code experts to East Tennessee for a one day conference open to all developers. This is not a trade show with slick salesman giving prepared demos – this is a gathering of real programmers learning about the latest in technology from each other. ”

Looks like a nice event, I’m going to see if I can swing it to go. You can also follow it on Twitterby following @CodeStock .

SQL Server Full Text Search – The ObjectPropertyEx Function

It can be quite useful to have a query that will list all of the tables in your database that currently hold full text indexes. Fortunately there is a function we can use, ObjectPropertyEx. We’ll combine it with a system view named sys.tables which gives us a list of all the tables in the current database.

Note, for todays example I will continue to use the AdventureWorks database, AdvWorksCatalog, and index I created in yesterday’s post on the FullTextCatalogProperty function.

ObjectPropertyEx takes two parameters. The first is the object ID of the table we want to work with. That’s why using sys.tables view is so nice, it already containts both the name of the table and it’s object ID. The second parameter is the name of the property we want to get, in this case TableHasActiveFulltextIndex will tell us if the table has an FTS (Full Text Search) index or not. Here’s a query that will list all of the tables with a full text index:

select t.[Name] as TableName

    , ObjectPropertyEx(t.[object_id], ‘TableHasActiveFulltextIndex’) as IsFullTextIndexed

from sys.tables t

where ObjectPropertyEx(t.[object_id], ‘TableHasActiveFulltextIndex’)  = 1

order by t.[Name]

TableName            IsFullTextIndexed
——————– ——————
ProductDescription                   1

This returns a list of the table name, and the value of 1 indicating the table does have a full text index on it. To get a list of all tables, simply omit the where clause. Tables without an full text index will have a 0 for the IsFullTextIndexed value.

We can further extend this function by using a second property, TableFulltextItemCount. This will tell us how many rows exist in the full text index for this table.

select t.[Name] as TableName

    , ObjectPropertyEx(t.[object_id], ‘TableFulltextItemCount’) as NumberOfRows

from sys.tables t

where ObjectPropertyEx(t.[object_id], ‘TableHasActiveFulltextIndex’)  = 1

order by t.[Name]

TableName           NumberOfRows
——————- ————-
ProductDescription           762

In this case the number of rows matches the number of rows in the table.

This is because I have change tracking set to auto, and have a speedy system. Be aware there are times when this number won’t match the row count. This would primarily be when you have change tracking set to manual, have inserted new rows in to the source and have not issued the command to force the update of the full text index.

By comparing the TableFulltextItemCount to the number of actual rows in the source table, you can monitor your system health and activity. If you see the numbers grow ouside the normal boundaries, it can be an indication something is wrong. You can also use it for adjusting the frequency of manual update commands. If the numbers grow apart too much for your comfort, you may wish to make the updates occur more frequently. Conversely, if most of the time the numbers match you may wish to reduce the frequency of manual updates.

It’s not absolutely required to go to the sys.tables in order to get the count. If you already have the name of the table, you can use it in combination with the object_id function to get the count for that one table.

select ‘Production.ProductDescription’ as TableName

    , ObjectPropertyEx(object_id(‘Production.ProductDescription’)

                        , ‘TableFulltextItemCount’) as NumberOfRows

TableName           NumberOfRows
——————- ————-
ProductDescription          762

Using the above logic you could easily create a function to pass in the name of a single table and get the number of rows in the full text index.

As you can see, using ObjectPropertyEx with its two simple full text search related properties will give you some useful functionality for monitoring your system.

SQL Server Full Text Search – The FullTextCatalogProperty Function

There is a useful function built into SQL Server for dealing with Full Text Search Catalogs: FullTextCatalogProperty . It takes two parameters, the first is the name of the catalog, the second is the name of the property you want. While there are quite a few properties in the list, most of them have been depreciated. However, there are still a handful that can provide valuable information.

For todays examples, I am using the AdventureWorks database. I’ve created a catalog named AdvWorksCatalog, using the following command:

create fulltext catalog AdvWorksCatalog as default;

I then created one index, using this command:

create fulltext index on Production.ProductDescription

  ([Description])

  key index PK_ProductDescription_ProductDescriptionID

  on AdvWorksCatalog

  with change_tracking auto

For more info on these commands, see my original series on Full Text Search which began in June of last year. (See the Arcane Lessons page for links to all the lessons).

The first property we will look at is IndexSize. You can call it up with the following command:

select FullTextCatalogProperty(‘AdvWorksCatalog’, ‘IndexSize’)

This will return the size, in megabytes (MB) of the indexes in the catalog. Since we only have one in this example, it is still quite tiny and thus will return a 0 for the result.

Next, let’s say you’d like to monitor the growth of the catalog by monitoring the number of items. To help with that, SQL Server provides a property named ItemCount.

select FullTextCatalogProperty(‘AdvWorksCatalog’, ‘ItemCount’)

In my case, it returned a value of 762 items in the catalog. An item is the same thing as a row in the source table. In my example, a select count from the Production.ProductDescription table also has a count of 762. These match since I’ve only created a full text index on one table in the database. In your system it will be the grand total number of rows for all the tables you have created indexes for.

A similar statistic is the UniqueKeyCount property, produced with this command:

select FullTextCatalogProperty(‘AdvWorksCatalog’, ‘UniqueKeyCount’)

This will tell you how many unique words were found in the catalog. In the Adventure Works database I’m using for SQL Server 2005, the count was 3,195. This would be like doing a select distinct word on the full text catalog. Each word may appear multiple times (once for each row it was found in) but will only be counted once in the UniqueKeyCount total.

The final property that is useful is PopulateStatus. This returns a value from 0 to 9 indicating what the full text engine is doing right now.

select FullTextCatalogProperty(‘AdvWorksCatalog’, ‘PopulateStatus’)

Since I didn’t want to constantly reference the on-line help to decipher the value, I added a little case statement to my SQL and will let it do the work:

select case FullTextCatalogProperty(‘AdvWorksCatalog’, ‘PopulateStatus’)

          when 0 then ‘Idle’

          when 1 then ‘Full population in progress’

          when 2 then ‘Paused’

          when 3 then ‘Throttled’

          when 4 then ‘Recovering’

          when 5 then ‘Shutdown’

          when 6 then ‘Incremental population in progress’

          when 7 then ‘Building index’

          when 8 then ‘Disk is full. Paused.’

          when 9 then ‘Change tracking’

          else ‘Error reading FullTextCatalogProperty PopulateStatus’

        end

There, much more user friendly. I would envision this would be most useful when preparing to do upgrades to the server. You’d want to be sure all full text activity had ceased before doing a server reboot, or applying a lot of SQL Scripts that altered the full text index structures.

In all of these examples, be aware SQL Server will return a NULL if there is an error. For example, you pass in the name of a catalog that does not exist, or the property is incorrect.

SQL Server Full Text Searching – Can you hear me now?

There are times when you might need to know, programmatically, if the Full Text Search component for SQL Server is even installed, and if so is it installed for a particular database. In SQL Server 2008 this won’t be an issue, as (according to the books on-line, and remember this is still an unreleased product) the full text search engine is integrated into the product and thus will always be there. However, in SQL Server 2005 the Full Text Search engine is an optionally installed component. Worse, by default the FTS engine is not installed, you have to explicitly install it.

Therefore, before setting up your app on a new server it would be a wise precaution to run a simple query and confirm the engine is there. It turns out there’s actually two methods for doing so:

select ServerProperty(‘IsFullTextInstalled’)

and

select FullTextServiceProperty(‘IsFulltextInstalled’)

both behave the same, they return a 1 if FTS is installed, if not installed they return a 0. Be aware they will also return a Null if there is an error (for example, you misspelled IsFulltextInstalled). If it turns out not to be installed, there’s not a great deal programmatically you can do to install new software on the server, you’ll have to have a manual intervention on the part of the system admin or DBA.

Once you have confirmed the engine is installed on the server, you should then check to see if the database has had full text enabled for it. Another simple query fills the bill. The first parameter we pass is the name of a database, the second is the name of the database property we wish to examine, in this case IsFullTextEnabled:

select DatabaseProperty(‘AdventureWorks’, ‘IsFullTextEnabled’);

Like the others, it returns 1 if it’s enabled, 0 if it’s not, or a Null if there was an error (for example, passing in a database name that doesn’t exist). Please note the DatabaseProperty does not indicate the presence of a catalog, it merely indicates whether the database has been enabled for full text searching.

Should the database not be enabled (a 0 is returned) you can enable it by calling a stored procedure:

sp_fulltext_database ‘enable’

This will enable full text searching for your current database. You can also call the stored proc with a ‘disable’ parameter to turn off FTS, but why would you want to ! 😉

By the way, to do most of this you will need DBA level privileges. That’s a logical conclusion, especially if you are calling these as part of a program or set of scripts to create or modify a database.

BarCamp Birmingham 2 Presentations

At last Saturday’s BarCamp Birmingham, I gave three presentations. The first was on Virtual PC 2007. For more info on it just look to my previous post, which has the first video on VPC. I’m currently working on the other videos in the series and should have them up this week.

My second presentation was “The Developer’s Experience”. As promised in the session, here’s the complete PDF of my slides: The Developer Experience. This has hyperlinks to all of the tools I presented.

My final presentation was on Full Text Searching on SQL Server 2005.  First, here is a PDF of the PowerPoint slides: Full Text Search Power Points

Next, most of the demos used SQL statements. This PDF file has all of the SQL plus some associated notes. Full Text Search Demo Scripts

Finally, I didn’t get to demo this at BarCamp due to time, but I do have a WPF project that demonstrated how to call a full text search query from a WPF Windows application. Annoyingly enough WordPress (who hosts my blog) won’t let me upload ZIP files, so I renamed the extension to pdf. After you download the file to your drive, remove the .pdf and put the zip extension back on, then it should expand all the source for you correctly. (Yes, I know, I really need to get a host server for binaries, one of these days I’ll get around to it, but for today…) Source for WPF Demo

See you at the next BarCamp!

Atlanta Code Camp – Introduction to SQL Server 2005 Integration Services (SSIS)

Thanks to everyone who stuck with me during my Saturday morning presentation at the Atlanta Code Camp. For those who didn’t make it, I had come down with either food poisoning or some sort of virus on Thursday night and was extremely sick on Friday. I had recovered enough Saturday to make the camp, even though my voice was just about gone. I promised to post my materials, so without further delay here they are:

First off, here’s the power point slides, in PDF format: Intro to SSIS Slide Deck

Next, here is the script I used to generate my demo: Intro to SSIS Script. If anyone has issues with the directions, please e-mail me and let me know, this is my first pass at this format and I want to ensure it’s usable for everyone.

Finally, here is the project:

SSIS Test 1_zip

I am feeling a bit better today, slept most of Sunday and while I’ve totally lost my voice my fingers still work so I wanted to get this out here.

Thanks to the folks in Atlanta for a great code camp, and thanks again to everyone who attended my session it was a great crowd.

The Virtual Meeting

We had an interesting event last week during our BSDA meeting. Doug Turnure from Microsoft was presenting at our BSDA group. I decided to Twitter the high points as Doug went through his presentation on SIlverlight. (I loved Doug’s line about the DLR being the Woodstock of programming languages.) During the event Shawn Wildermuth, The ADO Guy (who should probably take on the new name The Silverlight Guy) tweeted back with some interesting comments and links.

At the same time Doug was also recording the presentation using Camtasia. I’m hopeful the quality will be sufficient to distribute as we were using a new microphone in new surroundings.

My goal is to take as many of the BSDA’s meetings as I can to the virtual community. To record the presentations for later playback, and to provide an interactive experience for those who can’t attend. What about your user group?