January 15, 2007 at 9:44 am
Prior to SQL2005 - under tasks - one could script out stored procedures and select whether it was CREATE, DROP, ALTER, etc. With SQL 2005 you cannot create a "bulk" script of ALTER procedures. Does anyone happen to have a work around or is there something I missed?
What I need to do is grab approximately 100 stored procedures out of the database and do a find replace on a server name. The server name is changing and the sprocs need to be updated. Prior to SQL 2005, you could very easily do this - but they have seem to taken that functionality away for 05. Or does anyone out there have a 3rd party tool that replaces this functionality?
Any ideas would be helpful.
Thanks.
January 16, 2007 at 7:35 am
The solution I'm using isn't pretty, but it works. Here's what I do:
Create a script of all the stored procedures as "create to" file or clipboard. Once it's finished, I put the resulting script in a text editor (like notepad). Once there, I do a find and replace to change the "create" statements to "alter". Be carefull not to do a replace all though! If you do, it'll break any temp tables by changing the "create table" into an "alter table".
Hope this helps.
James
January 16, 2007 at 9:28 am
Thanks for the reply.
I ended up doing something along those lines. I really wish Microsoft didn't take that functionality out of SQL 2005. Sometimes, I don't understand why the remove certain functionality. I guess sometimes it makes sense, but if you've had that fucntionality all along, why change it.
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply