Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
West Wind WebSurge - Rest Client and Http Load Testing for Windows

Generate Scripts in Sql Management Studio


:P
On this page:

I frequently generate scripts for  SQL 2000 and it’s not uncommon when I use SQL 2005 that I use a feature that doesn’t work in Sql 2000, so the steps to do this are:

 

  • Run the Wizard
  • 9 steps later: Generate
  • Fail
  • Completely start over with 9 previous Steps 

 

Uhm, something’s not very efficient in this workflow, n’est pas? 

 

In addition the errors that get generated can be pretty sparse too. In one case I had a constraint type that doesn’t work in Sql 2000 and the generate option told me that there’s an error and shows me the first table that has the error, but no hint as to which field in this table is the problem. Now SQL has to know which field failed so why not give me a little break and TELL ME for crying out loud.

 

The new Generate Scripts option in SQL Management Studio is a royal pain in the ass to work with. Not that script generation was ever really clean in Sql Enterprise Manager but the current scripting wizard is painful to step through with its many options. But the worst problem is that once you’re done you can’t make a change to the settings and re-run instead you have to go back and start over.

 

How hard could it have been to have:

 

  • A back button to go back through the options
  • Save Options to pick them up on another pass
  • Save the last settings and use them for defaults on the next pass

 

But no <s>.

 

I realize there are a third party tools out there that do a much better job, but Database script generation is one of the core features that should at least be reasonably user friendly? Grrr… I just went through a script generation routine 4 times before I got it right.

Posted in Sql Server  

The Voices of Reason


 

Matt Brooks
March 09, 2007

# re: Generate Scripts in Sql Management Studio

Rick,

I share your frustration. At work we in-house release management software for releasing updates to our core legacy classic ASP/COM application platforms that reside on multiple production servers. The software handles SQL scripts for schema and stored procedure changes etc. In case a release fails these SQL scripts have to be re-runnable and guaranteed to leave the production server in the required state on the final successful run. A good way of doing this for stored procedure creation or updates is to use the DROP/CREATE/GRANT options of SQL Server Enterprise Manager. However, we seem to have lost this combined ability in SQL Server Management Studio. Instead I find myself having to generate all of the DROP commands in one pass, followed by all of the CREATE and GRANT commands in another pass, making sure to specify 'Append to file' or I have to start over!

Am I missing something here? Perhaps this is actually achievable somehow?

Rick Strahl
March 09, 2007

# re: Generate Scripts in Sql Management Studio

Hmmm I think Script Behavior options do this? Well, maybe not. I just looked at the option and the help file and now I'm thouroughly confused as to what that option accomplished. <s> You gotta love the help for the Wizard:

Options
Specify database scripting options by selecting from the available settings in the list box to the right of each option. The options are applied globally to the objects being scripted. Additional information about a selected option is displayed in the description pane at the bottom of the page.

Apparently this Wizard was an afterthought in every respect...


Not that this helps you, but for change management I use SQL Compare (http://www.redgate.com/products/SQL_Compare/index.htm) without which I wouldn't want to live. It takes minutes to create or even apply a change script to a server. I use it to keep live databases in sync as well as generating version update scripts for several applications that are self installed\updated by customers.

Stephen
March 09, 2007

# re: Generate Scripts in Sql Management Studio

- "Rick: I realize there are a third party tools out there that do a much better job"

What are some that you've had experience with?


- "Matt: Instead I find myself having to generate all of the DROP commands in one pass, followed by all of the CREATE and GRANT commands in another pass"

Service Pack 2 *finally* addresses this issue

Jesse
March 09, 2007

# re: Generate Scripts in Sql Management Studio

here's a free one I wrote that others seem to like. it will script any 2000 or 2005 database, with the data as well (optionally).

I even included a .bat file that would execute all the scripts (there's one file for each object) to create a fresh new db. I use it as an autobuilder for sql code.

http://www.elsasoft.org/tools.htm

hope you find it useful!

Jesse
March 09, 2007

# re: Generate Scripts in Sql Management Studio

forgot to mention that the source is available so if it doesn't meet your needs, you can alter it if you know a little C#.

Peter Bromberg
March 10, 2007

# re: Generate Scripts in Sql Management Studio

I use the SQL Server Database Publishing Wizard, which seems to do a very nice job (database, optional schema, and optional data also).

http://www.microsoft.com/downloads/details.aspx?FamilyID=29b4ffd8-ac3a-4481-b352-9b185619a901&DisplayLang=en

Rick Strahl
March 10, 2007

# re: Generate Scripts in Sql Management Studio

Peter - ah forgot about this tool, and it works well enough. One nice thing about it is that it can also script data which is nice.

It's also quicker to zip through the options, mainly because there are not very many, but it too doesn't allow you to go back through after you've generated.

Stephen, I've used APEX Sql Script before which is pretty nice, but pricey for just generating scripts. Red Gate's Sql Packager works along similar lines and I often use this for customer installs/update installations...

Andy Stephens
January 25, 2008

# re: Generate Scripts in Sql Management Studio

Actually you *can* set default scripting options. It's in Tools -> Options, then choose "Scripting". I'm sure you would have spotted it had it always been there, so perhaps this was introduced in a service pack? If it helps my version of SSMS is 9.00.3042.00.

Bizarrely it doesn't include *every* scripting option, for example "Script Drop" isn't in there.

West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024