Markdown Monster
Generate Scripts in Sql Management Studio
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.
The Voices of Reason
# re: Generate Scripts in Sql Management Studio
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.
# re: Generate Scripts in Sql Management Studio
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
# re: Generate Scripts in Sql Management Studio
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!
# re: Generate Scripts in Sql Management Studio
# re: Generate Scripts in Sql Management Studio
http://www.microsoft.com/downloads/details.aspx?FamilyID=29b4ffd8-ac3a-4481-b352-9b185619a901&DisplayLang=en
# re: Generate Scripts in Sql Management Studio
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...
# re: Generate Scripts in Sql Management Studio
Bizarrely it doesn't include *every* scripting option, for example "Script Drop" isn't in there.
# re: Generate Scripts in Sql Management Studio
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?