none
SSMS generate scripts problem

    Question

  • SSMS for SQL 2005

    This has happened to me twice now in the last week. When I'm actively developing a db, I often script the whole thing to a text file so I can search for things that I might need to deal with. The current db that I'm working on generates some temp tables (not # or ## but actual tables that are created and dropped as needed). If I try to script the db while the system is active and I select ALL the tables and any of them are dropped during the scripting process, the scripting process HALTS at that point and doesn't save anything it's done to that point. So if I have a db with 400 objects to script and it takes an hour and 1/2 to do them all, if it gets to some table that's maybe object 350 and stops then the more than an hour of time to script to that point is completely wasted. Who thought this bright idea up?????? It should continue scripting the rest of the objects and create the file or the new query window or whatever and warn you that it missed objects and why it missed them. But to stop altogether and completely abandon the operation and not save any of the scripting that's been done to that point is idiotic. Apart from carefully checking which objects to script or not, is there a way to tell the thing to keep going if it hits an error?

    Thanks,

    Keith

    Tuesday, March 27, 2012 7:12 PM

Answers

  • Hi Keithrh,

    >> there IS an option to continue on error. But in the general scripting settings in the Tools/Options menu, that setting does not exist.

    Please try to use Generate and Publish Scripts Wizard to Generate a Script because there is a Continue scripting on error option you wanted  by selecting a value from the list of available settings.

    When the value is True, scripting stops when an error occurs, otherwise scripting continues. The default is False. Please see the screenshot as below: 

    For more information ,please refer to Generate and Publish Scripts Wizard (Advanced Scripting Options Page)


    To open the Generate and Publish Scripts Wizard, please follow the steps: >>In Object Explorer, expand Databases,>> right-click a database, >>point to Tasks, >> click Generate Scripts.
    For more information, please refer to How to: Generate a Script (SQL Server Management Studio)


    Regards, Amber zhang

    Thursday, March 29, 2012 6:49 AM

All replies

  • Consider using Scriptio - it works a lot better than the built in 2005 ssms scripting.

    http://scriptio.codeplex.com/

    Tuesday, March 27, 2012 7:14 PM
  • I'll check that out. It's probably much nicer than ssms like you said. Thanks for the info. And I did find a solution just after my OP. In the scripting settings, you can set it to continue on an error. I'd been all through that list before. Not sure how I missed it. But it's there.
    Tuesday, March 27, 2012 7:18 PM
  • Well. Here's the problem. In the scripting options when you run a script, there IS an option  to continue on error. But in the general scripting settings in the Tools/Options menu, that setting does not exist. I set all my scripting options ahead of time there. I don't even pay attention to them in the other window. Seems like it sure would make sense to keep those options consistent!!! It would also be nice, MS, if you had a button on the scripting options window that comes up when you create a script to save those options to the main scripting options. Kind of obvious but oh well.
    Tuesday, March 27, 2012 7:21 PM
  • Hi Keithrh,

    >> there IS an option to continue on error. But in the general scripting settings in the Tools/Options menu, that setting does not exist.

    Please try to use Generate and Publish Scripts Wizard to Generate a Script because there is a Continue scripting on error option you wanted  by selecting a value from the list of available settings.

    When the value is True, scripting stops when an error occurs, otherwise scripting continues. The default is False. Please see the screenshot as below: 

    For more information ,please refer to Generate and Publish Scripts Wizard (Advanced Scripting Options Page)


    To open the Generate and Publish Scripts Wizard, please follow the steps: >>In Object Explorer, expand Databases,>> right-click a database, >>point to Tasks, >> click Generate Scripts.
    For more information, please refer to How to: Generate a Script (SQL Server Management Studio)


    Regards, Amber zhang

    Thursday, March 29, 2012 6:49 AM