February 12, 2009 at 4:55 am
If I begin a Transaction and in this transaction I drop procedures and recreate them, will these drop and create statements only take effect after the COMMIT statement?
Example:
BEGIN TRANSACTION
GO
if exists (select * from sysobjects where id = object_id('spt_nameHere') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
drop procedure spt_nameHere
GO
CREATE PROC spt_nameHere
AS
/* SP code here */
COMMIT TRANSACTION
February 12, 2009 at 5:06 am
Probably it will, but your existing object will not be available to others during your transaction and your db's catalog may get locked.
So keep you transactions as short as possible.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 12, 2009 at 5:50 am
Why would you want to do something like that?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 12, 2009 at 6:00 am
Well actually it's not me that wants to do this.
The question came from a developer working on a development server.
I guess he just wanted to know for interest sake - i sure would not advice it be done on production.
February 12, 2009 at 6:18 am
Ah. OK. Good answer. 😀
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 12, 2009 at 6:27 am
Actualy that's how I implement all scripts provided by an external party.
So during planned downtime (dev/qa/prod), just add a begin transaction on top of the script and if it fails, I can still perform a rollback.
This way I try to avoid restores because of faulty scripts.:hehe:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 12, 2009 at 6:49 am
But the key word and tricky phrase in that sentence was "during planned down time." I hear a question like this and I can sense my hackles going up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 12, 2009 at 6:58 am
Oh yes... I should have highlighted "during planned downtime"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply