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


  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’


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’)


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.

Installing Ubuntu 8.04 under Microsoft Virtual PC 2007

Update: Nov. 10, 2008 – New blog post on installing Ubuntu 8.10 is now out: http://tinyurl.com/vpcubuntu810

I’m pleased to say that Ubuntu 8.04 is probably the easiest install I’ve had to do with VPC yet! One quick reminder before we begin, when working inside the VPC your mouse will get “trapped” or captured by the virtual computer. You won’t be able to move outside of it. To get it released, just press the RIGHT side ALT key. Left side won’t work, has to be the RIGHT side of your keyboard.

To start with, create a new Virtual PC. For a tutorial, see either my step by step tutorial or the video tutorial if you need more instructions. Since I had the space, I was using 768 meg of ram, and left the disk space at the default of 16 gig. If you can, try and use at least 512 meg of ram for good performance. Use the CD menu option to capture the desktop ISO you downloaded from Ubuntu, or if you have a real CD put it in the drive and capture that. When it launches, you’ll see this screen. (By the way, you can click on any of the screens to see the full size graphic, these have been resized slightly to fit in with most common browser sizes).

[image - Select Language]

Pick your language, I just took the default of English.

[image - Safe graphics mode]

Now press F4 to select an alternate starting mode. When it pops up, change to Safe graphics mode, as you see above, and press Enter. Now pick “Try Ubuntu…” (should already be selected) and press enter. Do NOT pick the Install Ubuntu option, I kept getting VPC errors when trying to install directly.

Additionally, don’t be alarmed if the screen goes black for a while, then you see some garbled graphics. This is perfectly normal, it is just passing through and will be OK when Ubuntu gets done doing it’s thing. It took me about 7 minutes to get from the previous screen to the next one.

[image - live environment]

After it boots you should be in the live session trial environment. Double click the Install icon to begin the install process.

[image - Installer welcome screen]

Screen 1 is just a welcome screen, although you can change your language here if you need to. Press Forward to continue.

[image - Installer Set Time Zone]

Next it wants to know where you are, at least time zone wise. I’m in the central time zone, but set yours appropriately and click Forward.

[image - Installer Pick your Keyboard]

Next you can specify your keyboard. Since I’m using a typical USA style keyboard, I just clicked Forward.

[image - Installer Prepare Disk Space]

Next it asks how you want your disk space partitioned. Since we’re in a virtual environment, it made the most sense to just take the defaults and click Forward.

Be aware, after clicking forward my mouse went into the “I’m busy” mode, and there was a delay while the disks were prepared. Mine went about five minutes. Don’t be alarmed, just wait a few minutes and you’ll then proceed to the next screen.

[image - Installer Who Are You]

On this screen, first supply your name; this will be used in documents and the like. The next text box is the important one – it is for your Ubuntu user name. By default it uses your first name, now is your chance to change it. I rather like mine so will accept it. Next you’ll need to key in a good password and confirm, and finally name the computer. When you are happy, click Forward.

Now is where you may get confused. In the screen above, you are on step 5 of 7. When you click forward, you are suddenly on step 7 of 7. I’m not sure what happened to step 6, I even ran the installer yet one more time just to make sure it was gone. Perhaps it was kidnapped by space aliens?

[image - Installer is Ready]

Apparently even without the missing step 6, the installer has everything it needs. Just click Install to begin the install process. Kick back and wait. Don’t be alarmed if the screen goes black during the process, it’s just the screen saver kicking in. Just click in the VPC and wiggle your mouse and your display will return. I had it kick in several times during the 45 (or so) minutes it took to get everything installed.

[image - Install complete time to reboot]

Eventually Ubuntu will complete it’s install, then give you the above message. On the Virtual PC menu click CD, then release the cd. Then click on the big Restart now button inside VPC.

This was the only real snag I hit in the whole install process, I waited a while and it never did restart on its own. I gave it about five minutes, then in the Virtual PC menu I clicked Action, Reset. I figured since it’s already installed, I wouldn’t lose anything, and I was right.

The boot process does take a few minutes; you’ll see some text then a black screen for about 90 seconds. Then it comes up to the big Ubuntu logo and the orange bar as it loads. You’ll see some garbled graphics for a few seconds, then the login screen finally appeared. I gave it my user id and password, and minutes later I was in Ubuntu.

One last piece of business, fixing the networking. First make sure the network card is mapped to a real network card in your computer. For more instructions on this, see my video, Virtual PC Advanced Settings. After that, click on the network icon in the upper right side of the toolbar, as you see below.

[image - Fix Networking]

Then just pick Wired Network. Once connected you’ll be free to visit your favorite websites!

[image - Ubuntu open for business]

I haven’t had much time to check out other features, or get the sound working so if anyone has a quick fix for that by all means leave a comment below.

Virtual PC 2007 Step by Step – The Advanced Settings Video

As I promised last week, here is the second video in the Virtual PC Step by Step series. This video covers some of the advanced settings available in Virtual PC, and explains what the ramifications are for changing each setting.

If you don’t wish to watch in the flash player, or want the best video quality, I would suggest downloading the 20 megabyte WMV file from this link:

This video is the second in the VPC series, you can find the first one on this post from last week.

I would appreciate some feedback, do you like the video format? Is it useful? I’m enjoying producing the videos, but I do find it takes considerably more time than a regular blog post. Let me know your thoughts in the comments area.

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!

Virtual PC 2007 Step by Step – The Video

Some time back I wrote a post titled Virtual PC Step By Step, which detailed the steps to setup a new machine in Virtual PC. Since we now have a new version (VPC 2007) I decided it was time to update the post. Only now I’ve done it in video form! Yes, Arcane Code is now producing videocasts for the web. This is the first in what I plan to be a series of video content.

Without further delay, here is the video in flash player:

Or you can download the 640×480 WMV version here (about 8.5 megabytes in size):

This is my first, so let me know how this works for you. Still working out various video editing settings, as well as the many options for WordPress. Your feedback will help to improve future productions.

Installing Ubuntu 8.04 Beta under Virtual PC 2007

Update: April 24, 2008 Today Ubuntu 8.04 was released, and it’s a much easier install. Ignore the directions below, and instead jump to my post for April 24th, 2008 – Installing Ubuntu 8.04 under Microsoft Virtual PC 2008.

Like a lot of folks I was interested in working with the latest Ubuntu. However, I had some real issues getting it installed under VPC. On the good news side the pesky mouse problem is gone, apparently the kernel got updated to fix the mouse bug that plagued us with 7.10.

A quick disclaimer, the instructions below were created using BETA software. As new betas trickle out, or the final version is released, some of the things here may or may not work for you.

I do have some bad news. Attempting to install the standard desktop version, when the install got 6% into the Partitioning phase the VPC either locked up, or crashed all together with an unrecoverable error. Now for the good news, I did find a way to get Ubuntu 8.04 Beta installed, but it took some work on the post install to get the display to work. Hang with me though and I’ll show you the steps.

First, you need the right bits. Go to the http://www.ubuntu.com/testing/hardy/beta website . Scroll past the download area and find a mirror that is appropriate to your area. I chose http://mirrors.easynews.com/linux/ubuntu-releases/8.04/ as I live in North America, but all of the pages look the same.

Once on the mirror site, scroll down past the Desktop area, down past the Server section, until you find the “Alternate install CD” area. That’s the one you want! I used the one for the PC Intel platform.

OK, fire up Virtual PC 2007 and create a new machine, I’ll assume you are familiar with VPC and won’t recreate those instructions here. Only note I’ll make is to make sure and pick ‘Other’ for the OS, and for memory you need at least 256 meg of ram for Ubuntu, 512 would be better. Of course if you have more, feel free to bump it up to 768 or even 1024. Since I had the available ram I used 768.

Boot the machine, and use the CD menu to mount the Alternate Install CD ISO that you had downloaded. After it boots you’ll see this screen:


Just press Enter to continue to the next screen.


Again, all we have to do is press Enter to continue.


Now select your native language. Since mine is English all I had to do is press Enter.


After picking my language, Ubuntu further wants to narrow in on where I live by asking about my country. I picked my country and pressed Enter.


Next Ubuntu wants to detect my keyboard. If you want you can go through the default of Yes, and walk through a series of screens where you press a letter on your keyboard, or tell it you don’t have that key. Frankly I found it much faster just to tell it what kind of keyboard I had, and picked No and pressed Enter.


Now it’s asking what the origin country of my keyboard is, since mine is USA (the default) all I had to do is press Enter.


Next I’m asked what keyboard type. If you want to use something different (I know a few fans of Dvorak) you can pick those, but since I’m using a standard keyboard, I just went with the default you see above and pressed Enter. A

After doing so, the installer went through a series of screens where it was doing a lot of scanning of my (virtual) system and installing various components. It took about 10 to 15 minutes to get to the next screen.


Here we are asked to give our system a name as it will be known on the network. The default name is ubuntu, but I modified it to what you see above so it would be unique. I would advise you to do the same, in case you wind up running your VPC on a network with others. Once you name your system, tab to Continue and press Enter.


On this screen you are asked what time zone you live in. I live in the great state of Alabama, which is in the Central time zone, select the time zone for your state and press Enter.


Next we are asked about partitioning the drive. I just took the default that you see here and pressed Enter.


Now Ubuntu wants to make sure we understand that it’s about to wipe out what’s on the drive. Since it’s just a virtual drive, that’s fine we can safely press Enter to continue without risking any damage to our host ‘real’ hard disk.


Ubuntu wants to make sure we really understand it’s about to wipe the drive and create two partitions. It even makes the default No. Change the answer to Yes, and press Enter. At this point a rather long process begins, on my system it was around fifteen to twenty minutes. Your time may vary depending on your system speed and hard disk speeds.


Now we are asked for our full name. I just entered my nick name here, but you can put your full name if you wish. This will not be your login, but just used as the initial default in e-mails and the like. Once you enter a name, tab down to Continue and press Enter.


OK, now it is asking you what name you want to use for a login / user ID. Make sure you pick something good as you may be keying this often. By default the installer takes your first name and lower cases it. That’s fine for me, so I’ll just press Enter to continue.


Next you are asked for a password. Enter a good password, tab down to Continue and press Enter. On the next screen, which I won’t bother to show you as it looks almost identical to this one, it asks us to retype the password. Do so, again tab down to Continue and press Enter to proceed to the next screen.

Ubuntu will do a short install, then show you this:


Ubuntu wants to find out if you need to use a proxy server. If you do, enter it here. Most people do not, so all you have to do is tab down to Continue and press Enter.

One last question from Ubuntu, asking about the system clock:


While it’s true many servers are set to UTC, most home machines are not, so I took the NO option and pressed Enter. On my system it took about an hour before I got to the next screen. It didn’t hang up through most of it until the very end. It stayed at 97% – Cleaning up… for quite a bit. But eventually it moved on to the next screen.


OK, you may think this is the finish line, but not quite. We still have to edit two files before we can use the system, otherwise we wind up with garbled graphics. When you press Enter on the screen above to reboot, look very carefully at the screen during the reboot. When you see the line:

Grub Loading Please Wait…

Immediately press the ESC key, and you should see this menu.


Change the menu to the second option and press e to edit the line. This will bring up the next screen.


Move to 2nd line and press e to edit. At the end of the line, add a space then vga=791 . Press Enter to save the change, which returns you to the above screen. Now press b to boot. The system will work, the screen may even go black for a minute, but eventually you will bring you to the following display.


Change option to root – drop to root shell prompt, tab to OK and press Enter. This will drop you to a command prompt.


At the command prompt, type sudo nano /etc/X11/xorg.conf and press Enter. This will bring up the nano editor, and let you edit the file that controls system settings.

Scroll down until you find the section titled Section “Screen” . At the bottom add the line DefaultDepth 16 as you see in this snapshot:


I put spaces, not tabs between DefaultDepth and 16. Once done, press CTRL+O (that’s the letter O, not a zero) to writeOut the file, then CTRL+X to close the editor.

OK, one more file to edit. Now type in sudo nano /boot/grub/menu.lst as you see in this screen snippet, and press Enter.


Now scroll down to the very end of the file. What we are looking at is the menu that appears when you press the ESC key on the GRUB loader. We are going to modify the first item, which is the default, to boot in VGA 1024×768 16k color mode.

Find the first line that says kernel at the end of the file (see where my mouse is pointing in the screen capture below.


Hit the END key to go to the end of the line. The line ends with quiet nosplash. Remove those two words and replace them with vga=791 , as you see below.


Hit CTRL+O (that’s the letter O for writeOut) to save it, then CTRL+X to exit the nano program. When you are returned to the command prompt, type in sudo reboot and press Enter to reboot Ubuntu.

This time, don’t press anything at the grub menu, just let it load. What I found was during the boot you wind up with a black screen that lasts about one minute, but if you carefully observe you’ll notice your drive light flashing. It then returns to a text screen, and eventually you are presented with a pretty screen asking you to login.


Enter your user name and press Enter, then on the next screen type in your password and press Enter again. And after a minute or two of churning… TA-DA!


I’m still looking into things like networking and sound, but at least these instructions will get you up and running with the Beta.

As I said at the beginning, these instructions are based on BETA code. When the final version is released sometime this month things may or may not change. Feel free to post your experiences in the comments area, along with any tips you may have on getting up and going with things like networking and sound.


Just thought I’d pass along information about BarCampBirmingham2 – April 11 & 12. The unconference conference.

What is BarCampBirmingham?

BarCampBirmingham is a user generated conference created around an open, participatory workshop-event, with content provided by participants. Those participating choose the session topics for the day and then present to each other. It’s free. It’s fun. It’s a great way to meet the local technology community.

What topics are being presented?

That is up to you. You can visit the BarCampBirmingham2 website (which is a wiki) and add any idea you have to the list of topics. If you have one in mind that you would like to speak on, put your name by it.

Where do I sign up?

Visit the BarCampBirmingham2 website and add your name as a Camper. You can also subscribe to the BarCampBirmingham Google Group to stay in the loop on the planning efforts.

How Can I Help?

Participate. Publicize. Present. This is an event by the people, for the people. An un-conference that takes direction from those who participate, not the other way around.

You can also:

See, I TOLD you Apple is the Evil Empire

Some time back, I wrote a blog post describing Apple as “The Evil Empire”. Now a nationally known figure is adding her voice to the chorus. In this CNet Buzz Report, Molly Wood describes Apple as her “bad boyfriend”. They The guy who forces her to look good, tell her what cell phone carrier to use, etc without caring about her.

I thought that was a pretty apt description, and it really helps delineate the differences between the Apple philosophy and everyone else. Apple keeps tight control over their domain. Who cares if the new Air only has 1 USB, no firewire, no internet, no optical drive, no media card reader, and no expansion slots? Hey it LOOKS good. And those pretty new i-Phones? Oh, you can only use the carrier THEY pick out for you. Third party apps? Only if they give their blessing, which they still haven’t done. But hey, it LOOKS good.

Contrast that with both Windows and Linux. You can run the OS on any machine you wish. Windows Mobile? sure, any company who wants to license it for their device or carrier great with them. Heck Microsoft was even so open they let the i-Phone work with Exchange. But people complain that Microsoft isn’t open enough? And after Apple’s latest stunt of trying to force Safari down everyone’s throats via the iTunes update, I’d better not hear any Mac-Head deride the Windows Update process as “sneaky”.

It’s no wonder people are resorting to installing Windows on their MacBooks, it’s the only way they can get the freedom to get any work done!