Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-16 : 10:06:01
|
| adabala writes "I have written a Sql server stored procedure I am using distributed transaction .. I am accessing two databases from two servers and I am updating/ insering data in both the databases in one transaction. For that whenever I start a transaction in stroed proc I have written like this:BEGIN DISTRIBUTED TRANSACTION SET XACT_ABORT ON To update the tables in the distributed transaction I need SET XACT_ABORT ON ... But if the XACT_ABORT is ON . If any problem occurs in the transaction it automatically rollbacks the transaction.... Actually I want to do the rollback process externally. I mean whenever I get an error I want to check the error no and based on that I want to rollback the transaction... But because is SET XACT_ABORT ON it is rollbacking automatically and comming out of the stored proc... Could u please send me solution for this." |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-16 : 15:13:52
|
| i might be missing something but the solution sounds simple here. if you don't want the effects of SET XACT_ABORT ON, don't put it in the proc. if you want to check an error code before issuing a ROLLBACK, check for @@error after each dml statement and put your conditional logic there.setBasedIsTheTruepath<O> |
 |
|
|
mfautley
Starting Member
1 Post |
Posted - 2004-08-02 : 10:43:56
|
| I have a similar problem, and yes you are missing something rather important. For some types of distributed transaction, you MUST set XACT_ABORT to ON, other it won't open the distributed transaction in the first place.So how can I can insert data in my remote database, but if this hits an error, then I can still choose to commit or rollback my original transaction? At present, because I am forced to use XACT_ABORT ON, SQL Server bombs out immediately, no choice. |
|
|
|
|
|
|
Problem with SET XACT_ABORT