September 25, 2008 at 7:53 pm
Strange as it may seem, I've never had to generate a script in 2k5, yet. When I finally did (tonight), I found out that the script for a UDF is generated as dynamic SQL... single quotes have been double up and all the stuff to turn all my code into dynamic SQL for sp_executesql has been included. I'm normaly a bit more reserved on the forums but I have to say, WTF!
Is there a setting somewhere that will change how scripts are generated back to those similar to what Query Analyzer used to generate when you right clicked on a UDF and selected Generate Script...????
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2008 at 8:07 pm
Wow, that's surprising. Wonder if that happens in 2008. I'll have to check that.
September 25, 2008 at 8:48 pm
It's in the scripting options you pick. If you don't choose "script CREATE", it will default to the ALTER syntax. (which doesn't do the sp_executeSQL thingie from what I can tell).
----------------------------------------------------------------------------------
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?
September 25, 2008 at 8:54 pm
Of course - the "tasks", "generate script" option for a single object is the old Howitzer as a fly swatter. Just right-click on the function and use the "script function as" pathway instead. That never seems to turn stuff into scripts for me.
----------------------------------------------------------------------------------
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?
September 25, 2008 at 9:23 pm
Hmm, I am not sure of all of your conditions (obviously) but if the script needs to include an IF [NOT] EXISTS, then the body will have to be Dynamic SQL.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 26, 2008 at 3:53 am
Go to SSMS/Tools/Options and select Scripting. There's an option there to include 'IF NOT EXISTS'. If you set that to False, you should get what you want.
Cath
September 26, 2008 at 5:18 am
Barry and Cath, that did the trick. Setting "Include IF NOT EXISTS clause" to false, got rid of all the dynamic SQL.
Matt, yeah, I'd already tried ALTER and that, too, created the script as dynamic SQL.
It's funny... they went through all of that trouble to protect you from an accidental overwrite and whether it actually creates the script or not, you get the same message at the end of the run...
Command(s) completed successfully.
No clues about whether the object already existed or not which certainly protects you from an accidental overwrite, but leaves room for the error of code not actually being promoted. A simple "ELSE" with an "Object already exists" message would have made it perfect. What a waste of programming time on their part.
Trying to turn a fault into a feature, the cool part about this is that I'll never have to convert a proc to dynamic SQL ever again. But, that's done in every what... thousand procs or so? What an oversight on the part of MS. Who was the project manager on that part... I want to take him/her out for a porkchop dinner. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
September 26, 2008 at 6:06 am
I write a lot of my own script generators and then repeatedly execute the results and from my experience, it's not accidental overwrite that they are primarily protecting you against, it is the error messages themselves. You see, when you are executing a script with thousands of items, you do not want spurious error messages because that makes it impossible to find the significant ones.
Like say, you execute a script to create 1200 objects in a new schema, but some of the objects failed because something was not set right. Well after you fix that setting, what you would like to be able to do is just re-execute the script. However, if it throws 1100 error messages because of the objects that it previously created, you can not be sure that there are not other, valid, errors in there. And you do not want to have to generate *another* script just to drop all of the existing objects (especially if the schema already had some objects in it).
No what you really want is for the script to just skip over the objects that already exist, which is what it does. This is a feature called "Restartability" which is one of the requisite attributes for what we used to call "Robust Operational Scripts/Procedures"
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 26, 2008 at 6:15 am
Jeff Moden (9/26/2008)
No clues about whether the object already existed or not which certainly protects you from an accidental overwrite, but leaves room for the error of code not actually being promoted. A simple "ELSE" with an "Object already exists" message would have made it perfect.
I agree, this would be nice, but only as an option. Better yet, would be the option of ALTER scripts if the object already exists.
However, you can get the same functionality, just by generating a DROP script also and executing that first. In fact in SP2 of SSMS, it will (finally) offer you the ability to generate the DROPS and the CREATES in the same script. And in 2005, that is (mostly) in the correct order.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 26, 2008 at 7:46 pm
Heh... yep... I understand all of that. But the obvious intention of generating the dynamic SQL is to make it possible to protect the user from accidental overwrites when building new code... my bitch is that they didn't complete the intention. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
September 26, 2008 at 8:16 pm
Jeff Moden (9/26/2008)
Heh... yep... I understand all of that. But the obvious intention of generating the dynamic SQL is to make it possible to protect the user from accidental overwrites when building new code... my bitch is that they didn't complete the intention. 🙂
Actually - funny you should mention that. I usually script the "if exists" and "create" which will NOT overwrite the object if it's there.
Only when I want to replace do I add in the drop as well ,and then the script does it all in one swoop.....
----------------------------------------------------------------------------------
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?
September 26, 2008 at 8:24 pm
Jeff Moden (9/26/2008)
Heh... yep... I understand all of that. But the obvious intention of generating the dynamic SQL is to make it possible to protect the user from accidental overwrites when building new code... my bitch is that they didn't complete the intention. 🙂
No, that' is what I am disagreeing with, Jeff. Their intention never was to prevent accidental overwrite. There was never any danger of that, as executing the CREATE without the check could not overwrite the pre-existing object,, accidentally or otherwise it would just generate an error.
What the IF NOT EXISTS check was intended to do was merely to prevent the error message in the first place. And they did complete that intention.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 26, 2008 at 9:46 pm
rbarryyoung (9/26/2008)
What the IF NOT EXISTS check was intended to do was merely to prevent the error message in the first place. And they did complete that intention.
You could look at it that way... and they still failed to complete the intention. There's no indication that the new code wasn't promoted because the object aleady existed. In other words, their conversion of the code to dynamic SQL is all for naught without additional code by a human being... at least if you want it done "correctly."
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply