Use Advanced search when TFS does not find text in work items

Alert: Editing the TFS Collection database is tricky and should be done with extreme care!!

Recently I migrated a whole bunch of work items from a StarTeam repository. To keep some of the initial history, I added a some summary in the description field of the work item.

This looked something like this:

• Originally Created On: 20-07-2009 10:13:21
• Created By: StarTeam Server Administrator
• ExternalReference: 123456
• Last Build Tested: 7.3.0.9
• Starteam ID: 17525

All worked great but then…When searching for the text “7.3.0.9” in Web Access my Query showed no results.

The query that was constructed looked impressive though. Obviously it splits on the “.” sign

When I tried to search including quotes (“7.3.0.9”), it did not show any results as well. Hmm.Frustrating.

After some digging and with help of some guys at Microsoft (thanks Grant, Ewald and Hakan!), I found interesting thing.

The search over the text and html fields (like description, acceptance criteria, repro steps etc.) uses the Full Text Search capabilities of SQL Server to index the work items.

This MSDN article is a tremendous resource about how to optimally use the search box.

Anyways, the full text search ignores the “.” sign, along with a long list of other symbols and text.

Alert: Following these steps can result in a degradation of search performance!

In order to fix this, you need to add a stopwords list (a list that adds words to the full text index) to your TFS Database. To do this, follow these steps.

• Create a stopwords list in your TFS Collection database in SQL Server
• Add the stopwords list to your Full Text Index

Create a stopwords list in your TFS Collection database in SQL Server

• In SQL Management Studio, make a connection to your TFS Collection database.
• Expand the database node and expand the storage node
• Right click the Full Text Stoplists Node and choose [New Full-Text Stoplist]
• Give the list a name and click OK, Choose [Create an Empty List] and click OK
• Double click the newly created list and add the word. In my case the “.” sign

You can also run the following script against your TFS Collection database

CREATE FULLTEXT STOPLIST [RoadToALM]
AUTHORIZATION [dbo];
ALTER FULLTEXT STOPLIST [RoadToALM] ADD '.' LANGUAGE 'Neutral';
GO

Here you can find more information about StopLists

Add the stopwords list to your Full Text Index

Now you need to connect the list to your full text search. In order to that, you need to know a little bit about the TFS Collection database. This database, that you NEVER must edit, has a table called WorkItemLongTexts. This table contains all the content of text and html fields of all the revisions of work items.

This is also the table that is searched, so the stoplist must be attached here. In order to do this, execute the following SQL Command against your TFS Collection Database

ALTER FULLTEXT INDEX ON dbo.WorkItemLongTexts SET STOPLIST = RoadToALM
GO

More information about this command you can find here.

Results

When searching now, the “.” sign is included and when you now type the same query, the results are retrieved.

Hope this helps !