Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Full Text Catalog Scripting "Gotcha" in SSMS 2008

With every new version of SQL Server comes a set of deprecated features. Usually Microsoft gives fair warning that these features will be removed in a future version of SQL Server but they continue to work for the time being. In some cases, though, arguments for statements are left in place but have no effect on the current version of SQL Server. For example, take a look at the syntax for CREATE FULLTEXT CATALOG in SQL 2008. It's clearly stated that the ON FILEGROUP and IN PATH clauses have no effect in SQL 2008.

I use SSMS 2008 to manage my servers so that I can take advantage of the UI improvements that it offers. A common task I perform is to script objects from Object Explorer; since most of my production servers are running SQL 2005 I set my scripting options to target 2005 (Tools –> Options –> SQL Server Object Explorer –> Scripting). I haven't run into any problems with this until today. Even though my target is SQL 2005, SSMS 2008 is ignoring the ON FILEGROUP and IN PATH clauses when scripting out a full text index since they don't matter in 2008.

Here's what my scripted index looks like when I use SSMS 2008:

CREATE FULLTEXT CATALOG [Example Search Catalog]
WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT
AUTHORIZATION
[dbo]
GO

And here's what the index looks like when I use SSMS 2005:

CREATE FULLTEXT CATALOG [Example Search Catalog]
IN PATH N'E:\FTData'
WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT
AUTHORIZATION
[dbo]
GO

The same behavior occurs if you use the Scripting Wizard as well. As a general practice I do not run auto generated scripts without reviewing them first, and in this case I'm glad I did because my catalog wouldn't have been created where I wanted it to. Fair warning - there are probably more of these "gotchas" in SSMS 2008. If I find any more I will post them.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.