Generate Scripts option in SQL 2005 Management Studio

Did anyone even bother to test the Generate Scripts option for a database?

I changed the following options from their default setting:
"Include Descriptive Headers" = True
"Include if NOT EXISTS" = True
"Script for Server Version" = SQL Server 2000
"Script Indexes" = True

and scripted all Stored Procedures, Tables, User-defined functions, and Views

Problems:
1. Generates
IF NOT EXISTS (SELECT * FROM sys.objects ...
should be ...SELECT * FROM dbo.sysobjects ...

2. Generates
WITH (IGNORE_DUP_KEY = OFF) ...
on the indexes. That portion is incompatible with SQL 2000

3.Generates
EXEC sys.sp_addextendedproperty ...
should be EXEC dbo.sp_addextendedproperty ...

Is there a hotfix or patch to deal with these issues?

Additionally, the wizard only has options to generate Create statements or Drop statements. Where is the option to do both like the previous version had?

I hope patches and hotfixes are on their way soon.

Thanx,

Robert

Microsoft is aware of this problem and we are working on a fix.

Thanks

Grigory

|||Using SQL 2005 Management Studio, I need to script all selected SQL 2000 stored procedures sorted by the name of the stored procedure alphabetically. Using the script wizard, I select the stored procedures in the alphabetic listing, but it looks like the resulting script puts the stored procedures in some other rather random order. I used to be able to script in Enterprise Manager, but after installing SQL 2005 Management Studio with the SQL 2000 DTS Legacy addin, my Enterprise Manager will not work anymore. How may I create the alphabetic stored procedure script? Thank you.|||

I just wanted to add few more suggestions/annoyances in addition to those already mentioned regarding script generation that I have come across:

1. There could be a way to persist settings that one has chosen in the Generate Scripts dialog. IDEs are meant to boost productivity but how can this be achieved if one has to find each and every option whenever new script needs to be generated.

My suggestion: Make it possible to save script generation options. Better yet, save multiple sets of options as profiles - one to generate certain style of SQL 2000-compatible scripts, another for SQL 2005, etc. These profiles could then theoretically even be used for scripting using SMO :)

2. Is there any way to do the Generate Scripts type of thing on object level as it used to be in SQL 2000? While there are "Create To" and "Drop To" options on object level, these do not give any way to tune the scripts generation options. Its nonsence to be forced to go to the database level and start searching for the same object that I already had selected, in the wizard again!

My suggestion: Add ability to generate scripts in the same manner as the Generate Scripts task provides for single and/or multiple selected objects. The simple "Create To", "Drop To" are not enough. In my opinion SQL 2000 Enterprise Manager was much more streamlined in this sense.

I hope that fixes and additions will be available soon. Otherwise search for the tools that can create compatible scripts must begin...

Thanks

|||Hi,

Is there a patch for this problem yet?
|||

Any update on this?

Also, the ability to drop and create in one step is missing even though the option descirption implies it should be avialable.

Can you provide a time frame for a solution?

Glenn

|||I noticed the same problems... Just take note that users will start to wonder... Visual Studio seems to get it right.|||

Grigoriy,

Any chance the fixes for the topics in this thread will make it into SP1?

I'm interested in the DROP/CREATE being generated together (which the GUI says is an option) and I'd like to see the one stored-procedure-per-file output come back.

Thanks.

|||

I'm not sure if I'm missing anything in the wizard, but is there an option to script all object to a seperate file as in 2000? That was phenomally useful.

|||Where did you set these scripting options? I am trying to script a large number of tables along with their non-clustered indexes. All the CREATE script gives me is the clustered index.|||

Hi Mark

If you're using SS Enterprise Manager:

Right click on a database > All Tasks > Generate SQL script... (this will open the "Generate SQL scripts" box). On the Options tab you'll find an option under Table Scripting Options called Script indexes.

For SS Management Studio:

Right click on a database > Tasks > Generate Scripts... (this will open the "Script Wizard"). Select a database > click Next > In "Choose Script Options", scroll down to the bottom where you will find the option to "Script Indexes".

Max

|||Thank you. I didn't even know there was a Generate Scripts wizard, probably because there is nothing about it in the BOL.|||

I just stumbled across this post. Thanks Max for the description of how to get at the Script Wizard.

I would also like to add my voice to the requests that the backwards compatibility issues with scripting to SQL Server 2000 be patched soon.

|||Has this stuff been fixed yet? why market it as a feature when you know it doesn't work. Now I'm screwed
|||I'm just curious as to how this ever made it out of QA? I work in the dev dept of a mortgage wholesaler, building home grown .NET solutions. The kind of issues I've seen here and in other posts wouldn't make it past the first iteration. WTF?