THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is AndyLeonard.blog.

T-SQL Snack: Why Does SSMS Generate Create Scripts With EXEC dbo.sp_executesql?

This blog has moved! You can find this content at the following new location:

http://andyleonard.blog/2010/07/19/t-sql-snack-why-does-ssms-generate-create-scripts-with-exec-dbo-sp_executesql/

Published Monday, July 19, 2010 8:00 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ben Thul said:

No that there's anything wrong with your 'if not exists' block, but you're working too hard.  "if (object_id('your_schema.your_proc'), 'P') is not null)" should do the trick

July 19, 2010 7:24 AM
 

Aaron Bertrand said:

The reason you can't is because, at least today, the CREATE PROCEDURE must be in its own batch, and this would kill any surrounding IF blocks.  What would be nice is if you could point SSMS at the target database so that, like data dude / SQL Compare etc. it would know whether it needed to be a CREATE or ALTER.  Or if they would actually follow through with CREATE OR REPLACE syntax (without disrupting permissions and dependencies).

July 19, 2010 7:54 AM
 

Ben Thul said:

You're absolutely right; that's why you put a batch separator between the drop and the create.

July 19, 2010 9:48 AM
 

Eric Wisdahl said:

Man, that Matt Masson guy is MEAN! :-P

Any hints as to what else you requested from them?

July 19, 2010 11:53 AM
 

andyleonard said:

Hi Eric,

  I need to blog about this: I told them they broke the Package Configurations / Command Line order of operations and need to fix them. The command line should always win over an external configuration.

:{>

July 19, 2010 12:02 PM
 

AaronBertrand said:

Ben, the point is that the tool won't do this for you automatically.  Those of us who can write and modify our own scripts are not tripped up by the crap that SSMS can produce in some cases; the ones we're worried about are those who can't.

July 19, 2010 1:38 PM
 

Ben Thul said:

My comment is related only to the manually generated existence check that was placed at the end of the post.  I'm sorry if it was taken as commentary on the whole post (which, in retrospect, I can see how it might be interpreted that way).

July 19, 2010 5:43 PM
 

Ben Thul said:

I'd been meaning to put this up for a while and now is as good a time as any: http://blog.semperoccult.us/2010/07/scripting-your-database-for-fun-and.html

The formatting leaves much to be desired (I blame blogger and will try to figure it out), but the code shows that the so-called Shangri-la can be obtained programmatically.  Comments encouraged.

July 19, 2010 9:51 PM
 

LadyRuna said:

I wish they hadn't changed the scripting from how it worked back in SQL 2000. I preferred to have the "If exists... then drop <item you're going to create>" at the beginning of the script so that we would be able to save one copy of each script rather than an ALTER and CREATE version to manage updates / changes to the scripts over time.

I think Microsoft's reason for this scripting change was because it's trying to encourage you to choose ALTER instead of CREATE if the item already exists because whenever you recreate an object it obtains a new ID and could cause issues in some cases. (although I've never seen issues with recreating an object, I once read that supposedly it does and that you're supposed to ALTER the item after it's been created rather than drop and recreate.)

July 22, 2010 11:53 AM
 

Eric K. said:

Apparently this has changed again recently in SSMS 2014.

Now, if you script a drop and create with 'Check For Object Existence',  it:

Creates the "IF EXISTS (...) DROP PROCEDURE" block

Creates an IF NOT EXISTS (...) block that uses dbo.sp_executeSQL to create a BLANK stored procedure with the specified name,

And finally uses ALTER PROCEDURE to alter the blank stub to contain the final stored procedure script.  

Apparently Microsoft is trying REALLY hard to avoid any GO statements in their script.  

Unfortunately, a GO is still required before the ALTER PROCEDURE and the script, as generated by SSMS2014 FAILS with the message

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

August 22, 2014 11:00 AM
 

Ramanathan said:

Really great help.. i thought re-installing the sql server will help..but the problem remained as it was. but this artcile solved it simply  thanks

January 5, 2015 11:12 AM
 

Tom K. said:

Yeah, I would tell Microsoft, no the pony is not mine, but that does not give you the right to continually abuse it!  

The "IF EXISTS" is my "go to" for keeping a full, cumulative change log of scripted objects for our deployments. Now I either need to manually add them, or monkey with the sp_executesql to get it back. The CREATE/ALTER is nice to finally arrive, as Oracle has had this since the beginning, but I cannot believe they would expect this as the first statement in the batch.  I could understand the concern for reusing OBJECT_IDs, but we do not tie anything in our database to the OBJECT_ID.  Of course, this may be used extensively for some of the DMVs and might factor in for your db security.

Thanks Microsoft. Argghhhh!!!!!!

October 14, 2015 6:16 PM
 

giles said:

Ok. Maybe I'm the only person in the whole world that has to modify existing SQL Stored Procedures, but I do.

In 2000/2008 the scripts work perfectly - at least the way I think they should work.

In 2012, the issues is that with ALTER PROCEDURE or sp_executesql, the SQL is a string variable and INTELLISENSE DOES NOT WORK. Plus, it looks like S***!

Why, oh why, does Microsoft think they need to change everything every time they release a new version of a product! Forces us to re-learn stuff we already know.

January 22, 2016 12:46 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement