Advertisements
Home > Administration, SQL 2005, Tools > Missing Options in Script Generation in SSMS

Missing Options in Script Generation in SSMS

I was generating scripts in the Management Studio for all objects. Everything looked great; I created my new database with same structure.

Before I let the developer know database was good I decided to run it through ApexSQL Diff to see if there were any issues and sure enough it found issues with few tables.

Looking at it ApexSQL Diff found that three indexes did not get generated properly; in generating the indexes following options were ignored and never scripted out:

  • MAXDOP
  • ALLOW_ROW_LOCKS
  • ALLOW_PAGE_LOCKS
  • STATISTICS_NORECOMPUTE

Done on SQL Server 2005 SP1 (9.00.2198)

I have tried to do this via the Script Wizard and each Object with same result; I confirmed the database compatibility mode was 2005.

So I though okay maybe because I am running SQL Server 2005 SSMS SP1 thats where the issue is taking place.

So I decided to try it on SQL Server 2005 SSMS SP2 CU9, it came back with more information this time only option that was ignored was:

  • MAXDOP

Done on SQL Server 2005 SP2 CU9 (9.00.3182) and SQL Server 2005 SP2 CU9+Hotfixes (8.00.3310).

I have not used this setting on any index thus far; but if it being used be cautious when transfering objects from one database to another. Run it through tools like ApexSQL Diff or Red Gate Compare or the Microsoft Visual Studio Team System 2008 (Database Edition).

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: