none
Scripting option difference between SSMS 2012 and all previous versions

    Question

  • Hi,

    I recently started to use SSMS 2012 (11.0.2100.60) and have seen some differences in scripting behavior of this version with previous ones (since V9). In previous versions when I want to modify a stored procedure, I right clicked on it and selected “Modify”, and had a nice ALTER script. Now, I have a script using sp_executesql which is really not handy for SQL validation checks. After a research, I figured that I can change this behavior in the scripting options by changing the option “check for object existence”. Great, it makes the deal!! In fact, no, because now, when I ask to script a DROP and CREATE on a table, I do not have the IF EXSIST part before the DROP anymore.

    The behavior I try to have is:

    -          When I hit “modify” on a procedure or other programmatic part, I would like to have an alter statement (DROP CREATE is fine too).

    -          When I choose to script out an object as DROP and CREATE, I would like to have an existence check before the drop.

    This is basically what SSMS did before the version 11 and was very helpful.

    Regards,

    Stef, DBA


    • Edited by StefDBA Wednesday, August 01, 2012 5:49 PM
    Wednesday, August 01, 2012 5:48 PM

Answers

  • Finally, SSMS 12 works as 11. People like me will have to live with there disappointment...
    • Marked as answer by StefDBA Friday, August 29, 2014 8:00 PM
    Friday, August 29, 2014 8:00 PM

All replies

  • I just tested and see the same behavior.  I thought since the scripting option uses SMO behind the scenes that the version of SMO from 2012 is different that SMO from 2008, but after testing this is the behavior in 2008 as well.  I also changed the target type to 2008 from 2012 SSMS and still see the same behavior.  Here is a link I found with explanation of this behavior

    http://stackoverflow.com/questions/10620984/how-to-stop-ssms-2012-from-scripting-sps-using-sp-executesql

    http://sqlblog.com/blogs/andy_leonard/archive/2010/07/19/why-does-ssms-generate-create-scripts-with-exec-dbo-sp-executesql.aspx



    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz


    Wednesday, August 01, 2012 9:28 PM
  • Hi,

    Thanks for your reply.

    What do you think about the behavior?

    Personally, I checked the links you sent. I cannot agree with sentences like “who cares about the outcome of the DROP when the CREATE will still work”. When a script runs, it should run without errors, otherwise, how will you detect real issues in the noise? It’s like talking about “normal bug”. Also, “the create procedure must be in its own batch”, that’s what we did with the batch separator GO.

    A proper way to script database modifications (outside the tables itself) is to drop the object if it exists and to create it after. Why? Because it works in any cases! Also, when I try to modify code, it’s convenient to have validation check against, and that’s why it’s convenient not to have dynamic code. It’s like coding large web sites in notepad. That’s what SSMS 10.50 and below did perfectly.

    I use script DROP CREATE capability when I prepare a migration script. Why should I write code when I have tools doing it for me? As a DBA, I have to be critic about the script generated and to adapt it if necessary. The MODIFY option is when I debug some code. I expect the tool to validate as much mistakes as possible (yes I do mistakes and I admit it). Like I said before, it’s like working with notepad as coding tool, it works, but who will do it?

    I really would like to know if I’m alone on this position.

    Just to make it easier to understand for other people, the following code is what is generated.

    With SSMS 10.50.2806.0 (include IF NOT EXISTS clause = true):

    Scripting a table as “DROP and CREATE”:

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ScrapTable]') AND type in (N'U'))

    DROP TABLE [dbo].[ScrapTable]

    GO

    CREATE TABLE [dbo].[ScrapTable](

                    [ScrapID] [int] NOT NULL

    ) ON [PRIMARY]

    GO

    Right click on my stored procedure and hit “Modify”:

    ALTER PROCEDURE [dbo].[GetScrap]

    AS

    BEGIN

                    SET NOCOUNT ON;

                    SELECT * FROM dbo.ScrapTable

    END

    With SSMS 11.0.2200.60 (check for object existence = false):

    Scripting a table as “DROP and CREATE”:

    DROP TABLE [dbo].[ScrapTable]

    GO

    CREATE TABLE [dbo].[ScrapTable](

                    [ScrapID] [int] NOT NULL

    ) ON [PRIMARY]

    GO

    Right click on my stored procedure and hit “Modify”:

    ALTER PROCEDURE [dbo].[GetScrap]

    AS

    BEGIN

                    SET NOCOUNT ON;

                    SELECT * FROM dbo.ScrapTable

    END

    With SSMS 11.0.2200.60 (check for object existence = true):

    Scripting a table as “DROP and CREATE”:

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ScrapTable]') AND type in (N'U'))

    DROP TABLE [dbo].[ScrapTable]

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ScrapTable]') AND type in (N'U'))

    BEGIN

    CREATE TABLE [dbo].[ScrapTable](

                    [ScrapID] [int] NOT NULL

    ) ON [PRIMARY]

    END

    GO

    Right click on my stored procedure and hit “Modify”:

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetScrap]') AND type in (N'P', N'PC'))

    BEGIN

    EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE [dbo].[GetScrap]

    AS

    BEGIN

                    SET NOCOUNT ON;

                    SELECT * FROM dbo.ScrapTable

    END

    '

    END

    SSMS 11.0 does it definitely in another way.

    Regards,

    Stef, DBA

    Thursday, August 02, 2012 5:34 PM
  • You can download free ebook from Microsoft site for complete new features in SQL Server 2012.

    http://download.microsoft.com/download/F/F/6/FF62CAE0-CE38-4228-9025-FBF729312698/Microsoft_Press_eBook_Introducing_Microsoft_SQL_Server_2012_PDF.pdf


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Wednesday, August 08, 2012 5:21 AM
  • Hi

    Yes I found this problem as well in SSMS 2012. Behaviour of 'Check for object existence'  has changed across versions:

    SQL Server 2000: worked as expected

    SQL Server 2005: changed to 'EXEC dbo.sp_executesql ' format

    SQL Server 2008: back to old way

    SQL Server 2012: using EXEC dbo.sp_executesqlagain <sigh>

    This might not seem a big deal to most people, but we have a custom deployment app that relies on the old behaviour. It allows us to run our dev scripts multiple times against the same db and not throw errors.

    Stef: if you find a solution please let me know and I will do the same.

    MS: please consider putting this back the way it was in SQL Server 2008, thanks.

    Saturday, August 11, 2012 9:08 PM
  • Hi all,

    thanks for the answers.

    @assayent

    I use SSMS 10.50 for the moment to generate script. It's realy anoying to have tools behaving different from one version to the other. In version 12.0, we should be good again :-)

    Wednesday, August 29, 2012 11:19 AM
  • Yes to fix Tools -> Options -> SQL Server Object Explorer -> Scripting -> Check for object existence -> True

    Personally, our code must be SQL 2005 compatible so I change that option as well as well as script triggers and indexes and disable scripting of USE DATABASE.

    • Proposed as answer by Zeeshan Thursday, September 26, 2013 1:27 PM
    • Unproposed as answer by StefDBA Friday, August 29, 2014 7:54 PM
    Wednesday, September 19, 2012 4:23 AM
  • Thnx Sam...It resolved my issue now I have IF Exist check before dropping any object. 
    Thursday, September 26, 2013 1:28 PM
  • Finally, SSMS 12 works as 11. People like me will have to live with there disappointment...
    • Marked as answer by StefDBA Friday, August 29, 2014 8:00 PM
    Friday, August 29, 2014 8:00 PM