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.

About these ads

3 Responses to “SQL Server Full Text Searching – Can you hear me now?”

  1. SQL Server Full Text Searching at the Atlanta Code Camp « Arcane Code Says:

    [...] Can you hear me now? Checking to see if FTS is installed. Exploring SQL Servers FullTextCatalogProperty Function Using the ObjectPropertyEx Function Using FORMSOF in SQL Server Full Text Searching Creating Custom Thesaurus Entries in SQL Server 2005 and 2008 Full Text Search Creating and Customizing Noise Words in SQL Server 2005 Full Text Search Creating and Customizing Noise Words / StopWords in SQL Server 2008 Full Text Search Advanced Queries for Using SQL Server 2008 Full Text Search StopWords / StopLists [...]

  2. chipuff Says:

    Thanks a lot for the insight. We were experiencing timeout problems, “SqlException (0×80131904): Timeout expired”, not realising the FTS facility could be installed but not switched on for the particular database instance. Now all is good :)

  3. ScriptoX Says:

    Nice post..Keep them coming :) Thanks for sharing.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 106 other followers

%d bloggers like this: