Minor Detail, Major Impact in SSMS Script Wizard

After a few hours of pulling out what few stubs of hair I have remaining, I finally tracked down a rather perplexing problem relating to the Script Wizard feature of SQL Server Management Studio (SSMS).

My goal was to script out all of the stored procedures in a database in a fully re-runnable state, one SP per .sql file.  This would allow the procedures to be easily checked into source control, and tracked for changes that could be tied to our bug tracking system.  Having stored procedures and other database code in source control can be a tricky proposition because you need the right processes in place to ensure that any changes made to the source code are reflected in the database and migration path.  Conversely, it is equally important to ensure that any changes made directly in the database are accurately and promptly updated in source control.  Sound like a problem any of you have run into?  I know Ben Scheirman has, he posted a great summary of some database migration tools on his blog.

Although there are many good tools out there, my company is taking the “roll your own” approach because we have some specific requirements that no existing tool seems to encompass.  It has to handle SQL Server and Oracle, SPs, triggers, views, as well as ad-hoc DDL and DML changes.  It has been no picnic putting the processes and systems together, but I do think we have a good system in place.

One of the points of our approach is aided in the use of scripting tools, SSMS in particular for the SQL Server based database objects.  If you haven’t uncovered this handy tool (not very discoverable), try right-clicking on your database and following through Tasks to Generate Scripts:

I was scripting out a bunch of objects and trying to get home, so I thought I would try a little parallel processing to make the scripting go faster.  When choosing the settings in the SSMS “Generate Scripts” Wizard, I encountered the following differences between machine #1 and machine #2:

machine #1: Good

machine #2: Bad

Obviously, this smells like a software version difference, so I dug deep into the bowels of SQL Server’s Program Files folder (C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE) to find out that:

Machine #1: SqlWb.exe 2005.90.3054.0

Machine #2: SqlWb.exe 2005.90.2050.0

Unfortunately, Windows Update doesn’t pick up on this difference on 64bit machines!  So, unless you manually update your SQL tools, you’ll be stuck with inferior script generation options.  This is key because the options in the older of these 2 versions is not sufficient IMO.  Running Windows update on a 32bit machine yielded different results, and I was easily able to tell that I needed SQL Server SP2.  Obviously these are very specific situations, but I thought it was worth sharing

Bottom line is that you should have the following very helpful option in the Script Wizard in SSMS:

• Script Drop

If you don’t check to make sure you’ve got a recent version of the tools.  The previous version only allows you to “Generate CREATE statements only” or “Generate DROP statements only” which is not nearly as useful as tacking the 2 together.  A useful page for quickly searching through SQL Server release details can be found here: http://sqlserverbuilds.blogspot.com/