September 11, 2009 at 6:00 am
Hi,
We've got development and production database environments. We create tsql scripts to be run against the production environment as this is managed by another company.
Every time we update a stored procedure, we'd like to create a copy of the latest version in production and renaming it.
So spStoredProc would get copied and named spStoredProc_20090911. This is in case of a problem, so we can roll back the stored procedure.
Can anyone suggest a means of doing this in TSQL? Also is there a TSQL method of renaming a stored procedure without rescripting the entire procedure?
Thanks
September 14, 2009 at 2:39 pm
sp_rename is your friend for this one...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 14, 2009 at 4:20 pm
Use sp_rename to change the name, but be careful to identify the permissions on the procedure before you recreate it. Dropping and creating stored procedures removes the permissions and they'll have to be added back in.
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
September 14, 2009 at 9:17 pm
All of these things will work, but are you doing any source control? Don't you already have a copy of the way it was, and if not WHY?
CEWII
September 15, 2009 at 2:02 am
Elliott W (9/14/2009)
All of these things will work, but are you doing any source control? Don't you already have a copy of the way it was, and if not WHY?CEWII
We do have source control, but in my mind the best way to rollback a change is to revert back to the previous version. Having it in source control doesn't help this task. What's with the CAPS dude? Seems unnecessarily aggressive.
Thanks for the other responses, I've gone down the sp_rename route, and it seems to be working well for me.
September 15, 2009 at 3:14 am
singhster (9/15/2009)
Thanks for the other responses, I've gone down the sp_rename route, and it seems to be working well for me.
Glad to be of service.
Can I offer you one bit of advice with it - be careful if you script it out again - a lot of tools won't script out re-named procedures properly. Some will attempt to fix up the name and hash it up a right treat, some will fix it properly, some won't try at all. Try creating, renaming and scripting this proc in SSMS:
CREATE PROCEDURE
[dbo] -- schema
.
[procname] -- procedure name
AS
select 'hello'
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 15, 2009 at 6:35 am
singhster (9/15/2009)
Elliott W (9/14/2009)
All of these things will work, but are you doing any source control? Don't you already have a copy of the way it was, and if not WHY?CEWII
We do have source control, but in my mind the best way to rollback a change is to revert back to the previous version. Having it in source control doesn't help this task. What's with the CAPS dude? Seems unnecessarily aggressive.
Thanks for the other responses, I've gone down the sp_rename route, and it seems to be working well for me.
I only used caps on one word man, and source control is something that a lot of people here are not good at, or don't do, I wish to stress that they should and caps on a single word is not particularly aggressive.
I can see your rollback method but ask a further question, at some point do you do cleanup of old versions or do you just have old versions galore. We have typically used the last issued version from our source control to do rollbacks and not had ancient versions out there. I can see this working but am not a fan.
CEWII
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply