SQL Server Fulltext Search
How to install SQL Server Fulltext search on an existing SQL Server instance
Well after about 4 hours trying to figure this out, I have a solution ...
- To check if fulltext is already installed go to sql and run this query:
- Download latest installer for "SQL Server Express with Advanced Services" on the server (save it in "c:\installers" for example)
- Browse to where you saved it and Unzip the self extracting EXE (right click and click "Extract")
- Install it by double clicking setup
- Choose "upgrade existing instance to a new version or edition" and go through all steps
- I am pretty sure I was never asked what features I would like (ie no chance to choose Fulltext Search). When running the installer again there was also no way to "add features to existing instance", so eventually I figured out the following additional steps were needed...
- Go to Control Panel > Add or Remove Programs
- Find SQL Server and open it. (It asked me a question along the lines of "uninstalling this while other people are using it will cause them some pain" - it was a bit scary but I said yes OK and it then asked the usual question: do I want to unininstall or change)
- Question: uninstall or change (Answer: change).
- Click "Add Features"
- Browse for the install media. In this case we can locate the place where we unzipped the installer for "SQL Server Express with Advanced Services" (note it must be extracted or it won't like it)
- Go through a bunch of crapping around steps. You will then be asked "install new instance" or "add features to existing instance" (choose add features).
- At the "Features" step you can check the box "Fulltext Search" (hooray!!)
- Finish the wizard and everything should now work, run this sql to confirm:
Working with Full Text Catalogs
DROP FULLTEXT CATALOG catalog_name
Script catalog and indexes: (from: http://www.strictly-software.com/scripts/downloads/Script_Fullttext_Indexes.txt)
Author: Rob Reid Create Date: 12-Mar-09 Details: SQL script to generate the SQL required to script out a specified fulltext catalog and all fulltext indexes within that catalog. */ DECLARE @Catalog NVARCHAR(128), @SQL NVARCHAR(MAX), @COLS NVARCHAR(4000), @Owner NVARCHAR(128), @Table NVARCHAR(128), @ObjectID INT, @AccentOn BIT, @CatalogID INT, @IndexID INT, @Max_objectId INT, @NL CHAR(2) -- Specify name of catalog to script SELECT @Catalog = 'SystemHelp' SELECT @NL = CHAR(13)+CHAR(10) --Carriage Return -- Check catalog exists IF EXISTS(SELECT Name FROM sys.fulltext_catalogs WHERE Name=@Catalog) BEGIN -- Store the catalog details SELECT @CatalogID = i.fulltext_catalog_id ,@ObjectID = 0 ,@Max_objectId = MAX(object_id) ,@AccentOn = is_accent_sensitivity_on FROM sys.fulltext_index_catalog_usages as i JOIN sys.fulltext_catalogs c ON i.fulltext_catalog_id = c.fulltext_catalog_id WHERE c.Name = @Catalog GROUP BY i.fulltext_catalog_id,is_accent_sensitivity_on -- Script out catalog PRINT 'CREATE FULLTEXT CATALOG ' + @Catalog + @NL PRINT 'WITH ACCENT_SENSITIVITY = ' + CASE @AccentOn WHEN 1 THEN 'ON' ELSE 'OFF' END PRINT 'GO' -- Loop through all fulltext indexes within catalog WHILE @ObjectID < @Max_objectId BEGIN SELECT TOP 1 @ObjectID = MIN(i.object_id) ,@Owner = u.Name ,@Table = t.Name ,@IndexID = unique_index_id FROM sys.tables as t JOIN sysusers as u ON u.uid = t.schema_id JOIN sys.fulltext_indexes i ON t.object_id = i.object_id JOIN sys.fulltext_catalogs c ON i.fulltext_catalog_id = c.fulltext_catalog_id WHERE c.Name = @Catalog AND i.object_id > @ObjectID GROUP BY u.Name,t.Name,unique_index_id -- Script Fulltext Index SELECT @COLS = NULL, @SQL = 'CREATE FULLTEXT INDEX ON ' + QUOTENAME(@Owner)+'.'+QUOTENAME(@Table)+' ('+@NL -- Script columns in index SELECT @COLS = COALESCE(@COLS+',','') + c.Name + ' Language ' + CAST(Language_id as varchar) +' '+@NL FROM sys.fulltext_index_columns as fi JOIN sys.columns as c ON c.object_id = fi.object_id AND c.column_id = fi.column_id WHERE fi.object_id = @ObjectID -- Script unique key index SELECT @SQL = @SQL + @COLS + ') ' + @NL + 'KEY INDEX '+ i.Name + @NL+ 'ON ' + @Catalog + @NL + 'WITH CHANGE_TRACKING ' + fi.change_tracking_state_desc + @NL + 'GO' + @NL FROM sys.indexes as i JOIN sys.fulltext_indexes as fi ON i.object_id = fi.object_id WHERE i.Object_ID=@ObjectID AND Index_Id = @IndexID -- Output script SQL PRINT @SQL END END
The default for stop words is wrong, it means you won't be able to search for words like 'the', 'two', '2' etc.
To correctly set stop words setting, run the following:
sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'transform noise words', 1; RECONFIGURE; GO
Leave a Comment