SQL Server: How to set up Full-text search (FTS)

This article only applies to FogBugz 3, 4, and 5. FogBugz 6 and above no longer users FTS from the database. It is handled automatically by FogBugz.

If you are having trouble with search in FogBugz, or if you are setting Full-text search (FTS) manually, please follow this article.  There are several SQL Server specific things to check at the bottom of the page.

Step 1 - Enable FTS for the FogBugz database

Run this query in SQL Query Analyzer:

SELECT DATABASEPROPERTY('fogbugz','IsFulltextEnabled')

If you get a one, FTS is already enabled, so skip ahead to Step 2.

If you don't get a one, enable FTS by running these queries:

use fogbugz
exec sp_fulltext_database 'enable'
exec sp_fulltext_catalog 'FogBugzCatalog','create'

Now run this again:

SELECT DATABASEPROPERTY('fogbugz','IsFulltextEnabled')

If you get a one, good, FTS is now enabled. If you don't get a one, your installation of SQL Server cannot enable FTS, it may be that you need to reinstall SQL Server, or it may be that your SQL Server edition does not support FTS. In either case, there is no need to continue with these steps, but at the bottom of this article there are more links for support and forums.   

You should also check to make sure that FogBugz is aware that full text search should be enabled by checking whether fFullTextSearchOK is set to 1 in the Setting table of the FogBugz database by running the query:

SELECT sValue FROM Setting WHERE sKey='fFullTextSearchOK'

If that returns 0, run the query:

UPDATE Setting SET sValue=1 WHERE sKey='fFullTextSearchOK'

Step 2 - Set up a Full-text Index and Automatic Population

In SQL Enterprise Manager, drill down to Databases > fogbugz > Tables. Right click on the BugEvent table and choose "Full Text Index Table" from the menu. (If it is greyed out, full-text search is either not installed or just not enabled on your SQL Server.) Choose "Define Full Text Indexing."

The FTS wizard dialog pops up. Click Next. Choose the BugEventPrimaryKey and click Next:

Put a checkbox next to the item named s and click Next:

Note: "s" is the comments field of a case, so you are telling SQL Server to make all comments in cases available for full-text searching.

Choose the FogBUGZCatalog and click Next. (If it is not there, create a new catalog with that name, use the default location.)

On the next page you do not have to create a Population Schedule, just click Next, then click Finish, and say goodbye to the wizard.

Now right click on the BugEvent table and choose Full-Text Index Table > Start Full Population as shown below. Then also check off Change Tracking and Update Index in Background in the same menu, as shown:

 

Now we're going to run that wizard for the Bug table too. Right click on the Bug table and again choose Full-Text Index Table > Define Full Text Indexing. Choose the BugPrimaryKey and click Next. Put a checkbox next to the following items:

Choose the FogBUGZCatalog and click next, next, finish. Same as before, right click on the Bug table and choose Full-Text Index Table > Start Full Population. Also check off Change Tracking and Update Index in Background in the same menu.

And lastly, we need to make sure FTS is set up for the FogBugz Discussion Groups. Do the same steps as above, for the DiscussTopic table. The index should be called DiscussTopicPrimaryKey; select the table columns sHeadline, sFullName, and sPost; select the FogBUGZCatalog on the last page.  

Testing it out

Rather than testing search in FogBugz, just test it directly in SQL Server:

SELECT * FROM Bug WHERE Contains(sTitle, 'word')

Replace "word" with a single word that you know is present in the title of a case. The word should be 4 letters or more and contain no spaces. Once this query returns rows, FTS is succeeding in SQL Server.

Setting the Startup Service Account

SQL Server must start and run under the System account in order for Full-Text Search (FTS) to work. This is required in order for FogBugz Setup to succeed. Otherwise you may get an error message like this one:

Execution of a full-text operation failed.

You can set this correctly using Enterprise Manager like so:

Required Permissions

It's easy to break FTS in SQL Server. One easy way is to remove the BUILTIN\Administrators login from the System Administrators role. The following must be checked:

If you uncheck that, FTS simply won't return any rows. Full-text populations will fail since the MSSearch service needs this login to log into SQL Server. Such a failure will result in the following warning in Event Viewer:

So if FTS is not working, check the Event Viewer for errors from Microsoft Search, and check that you have the BUILTIN\Administrators login set up as described. Then run  "Start Full Population" on all 3 tables as described at the top of this article, and you should see this message indicating that what failed above is now succeeding:

Here is a set of articles from MSFT on this and related topics, outling options and alternative approaches.

Microsoft SQL Server Support

If you are getting error messages related to FTS, one of the following articles from Microsoft may also help:

More Resources