February 23, 2008 at 10:43 am
Hi all, this may be a simple fix, but I can't find any documentation on it, and noe of the Management Studio options seem to affect it. It seems that when I use the Modify or the Script Object to right click commands on my functions and procedure Management studio create the script as an sp_executesql string and not the old style CREATE, ALTER standard TSQL stement that I'm used to.
Does anyone know where this setting is?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 24, 2008 at 10:36 am
Jason Selburg (2/23/2008)
Hi all, this may be a simple fix, but I can't find any documentation on it, and noe of the Management Studio options seem to affect it. It seems that when I use the Modify or the Script Object to right click commands on my functions and procedure Management studio create the script as an sp_executesql string and not the old style CREATE, ALTER standard TSQL stement that I'm used to.Does anyone know where this setting is?
Tools | Options | Scripting
Set 'Include IF NOT EXISTS clause' to false.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 25, 2008 at 11:27 am
I was wondering the same thing. I tried the suggestion and it worked. Great, thanks.
But WHY? Why does including 'if not exists' cause it to use sp_executesql? Is that documented somewhere?
February 27, 2008 at 10:27 am
Thanks!
Although that's really odd that the "Drop if exists" setting controls the scripting of the entire object. :hehe:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 27, 2008 at 10:41 am
Patrick (2/25/2008)
I was wondering the same thing. I tried the suggestion and it worked. Great, thanks.But WHY? Why does including 'if not exists' cause it to use sp_executesql? Is that documented somewhere?
Just a guess but I'd say it's simply because it's easier to script it that way. CREATE .... usually wants to be the FIRST statement (sometimes the only statement in the case of CREATE VIEW) in a batch, so your IF statement would get broken if a GO appeared in the middle of it. It then becomes rather messy to conditionally not execute something that's not even in the same batch of code as you.
sp_executeSQL runs in its own scope, so create doesn't have anything to whine about.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 27, 2010 at 3:58 pm
Funny, though, in that this isn't how it worked IIRC for SQL Server 2000.
December 13, 2011 at 6:13 am
Thanks Jeffery, it worked for me also.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply