February 27, 2009 at 6:03 pm
Hello,
I ran a stored procedure in a database abc and I got errors. Now I want to rollback the changes.
In ORACLE, we have ROLLBACK; statement to rollback the changes But I did not find anything like that in sql server.Please suggest me how you guys are doing this?Provide me the steps plz...
Thanx
February 27, 2009 at 6:56 pm
Were you making use SET XACT_ABORT(http://msdn.microsoft.com/en-us/library/ms188792.aspx) in your stored procedure? If yes, then need not worry as SQL rollbacked evrything for u. If not, then it depends when and at what step u got the error.
MJ
February 28, 2009 at 3:18 am
rollback is the same in sql
Begin transaction
'Do your stuff
Rollback Transaction
There are a couple of ways you can check for errors
The old school way is to check for errors after each statement
begin tran
ie. insert into tab1
select 1
if @@error <> 0
begin
rollback tran
return
end
Commit transaction
The new way (since sql 2005) is to use a try/catch loop. syntax of this can be seen in books online but its a fairly simple
begin try
'do your thing
end try
begin catch
rollback tran
end catch
February 28, 2009 at 6:02 am
klnsuddu (2/27/2009)
In ORACLE, we have ROLLBACK; statement to rollback the changes
The statement is exactly the same in SQL Server. ROLLBACK or ROLLBACK TRANSACTION. The main difference is that in SQL, you have to explicitly start the transaction as well with BEGIN TRANSACTION
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply