March 11, 2014 at 1:00 am
Dear All
I have 2 Procedures. P1 and P2. Even if there is error in P1 , I want changes in the P2 to be committed. How do I do it?
Following is the code sample I have used for testing. With this code , update in both the procedures are rolled back.
create procedure P2
as
begin try
BEGIN tran
update table1
set Description = '*P2*' + Description
where Code = 'b'
commit tran
end try
begin catch
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
RAISERROR ( 'Erorr in P2' ,12,0 )
end catch
go
create procedure P1
as
begin try
Begin Tran
execute P2
RAISERROR ( 'error**' ,12,0 )
commit tran
end try
begin catch
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
RAISERROR ( 'Error in P1' ,12,0 )
end catch
March 11, 2014 at 2:59 am
Personally I've never done that, but since version 2008 there is an option to work with autonomous transactions with linked servers. You can read more about it here – http://blogs.msdn.com/b/sqlprogrammability/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx%5B/url%5D
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 11, 2014 at 7:56 pm
Thanks. There is no other way? Its possible in Sybase.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply