Full Text Catalog Scripting "Gotcha" in SSMS 2008

<div><p>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 <a href="http://technet.microsoft.com/en-us/library/ms189520.aspx" target="_blank">the syntax for CREATE FULLTEXT CATALOG in SQL 2008</a>. It's clearly stated that the ON FILEGROUP and IN PATH clauses have no effect in SQL 2008.</p> <p>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.</p> <p>Here's what my scripted index looks like when I use SSMS 2008:</p> <p><code style="font-size: 12px"><span style="color: blue">CREATE </span><span style="color: black">FULLTEXT CATALOG [Example Search Catalog] <br/></span><span style="color: blue">WITH </span><span style="color: black">ACCENT_SENSITIVITY </span><span style="color: blue">= OFF <br/>AS DEFAULT <br/>AUTHORIZATION </span><span style="color: black">[dbo] <br/>GO <br/></span></code></p> <p>And here's what the index looks like when I use SSMS 2005:</p> <p><code style="font-size: 12px"><span style="color: blue">CREATE </span><span style="color: black">FULLTEXT CATALOG [Example Search Catalog] <br/></span><span style="color: blue">IN </span><span style="color: black">PATH </span><span style="color: red">N'E:\FTData' <br/></span><span style="color: blue">WITH </span><span style="color: black">ACCENT_SENSITIVITY </span><span style="color: blue">= OFF <br/>AS DEFAULT <br/>AUTHORIZATION </span><span style="color: black">[dbo] <br/>GO <br/></span></code></p> <p>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.</p> </div>

Share This

• Twitter
• Google+
• Linked In
• Facebook

Related Posts

SQL Server 2005 SQL Server 2008 Tips