SQL Server Fulltext Search

1 comments

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 ...

 

  1. To check if fulltext is already installed go to sql and run this query:
    select SERVERPROPERTY('IsFullTextInstalled')
  2. Download latest installer for "SQL Server Express with Advanced Services" on the server (save it in "c:\installers" for example)
    http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=1842 
  3. Browse to where you saved it and Unzip the self extracting EXE (right click and click "Extract")
  4. Install it by double clicking setup
  5. Choose "upgrade existing instance to a new version or edition" and go through all steps
  6. 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...
  7. Go to Control Panel > Add or Remove Programs
  8. 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)
  9. Question: uninstall or change (Answer: change).
  10. Click "Add Features"
  11. 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)
  12. 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).
  13. At the "Features" step you can check the box "Fulltext Search" (hooray!!)
  14. Finish the wizard and everything should now work, run this sql to confirm: 
     select SERVERPROPERTY('IsFullTextInstalled')

 

 

Working with Full Text Catalogs

 

Delete: 


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

Stop Words


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



SQL

Comments


Leave a Comment