Transaction Handling Across Linked Server

Question

Post reply

Transaction Handling Across Linked Server

GristMill

SSC Enthusiast

Points: 181
More actions
February 2, 2006 at 2:26 pm

#166831

I am getting different results from my transactions than I am expecting. It goes like this.
set xact_abort on
Begin tran
query1 on local server
exec storedprocedure2 on linked server
query3 to local server
Commit tran
What I want to happen is if the any of the statements fail including the exec to the linked server, then all of the statements in the tran abort.
What I am getting is that if storedprocedure2 to the linked server fails, then I receive an error message that the transaction that started on the local server is committed/aborted. Then query 3 to local server executes in autocommit mode, and then I get an error that the code has an extra "commit tran". I.e., the failure of storedprocedure2 to the linked server aborts statements 1 and 2, but does not abort query3.
I have tried using "begin distributed tran". This did not change anything. Of course I can trap for the error, but I am wondering if there is a way to get all of the queries to abort without adding an error trap. The reason I think this is if I run the following code I get the behavior I want.
set xact_abort on
begin tran
query0 with inner join between table on local and table on linked server -- additional query preceeding
query1 to local
exec storedprocedure2 on linked server
query3 to local
commit tran
If storedprocedure2 fails in this scenario I get an error message that storedprocedure2 was committed/aborted (same as before). I also receive a message saying that i need to "start a new or NULL transaction". When query3 to local starts, instead of executing in autocommit mode, I receive an error message "SQLOLEDB was unable to begin a distributed transaction". And query3 does not run (nor any additional queries following in the tran). And the whole stored procedure stops.
In the case of the data I am working with, it is better to either run all of the queries successfully or none of them. So the second scenario is the behavior I want. It seems silly to run a fake query like query 0 just to get this behavior. How can I turn this behavior on?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 1

BEGIN DISTRIBUTED TRANSACTION might help you

Kindest Regards,

Vasc

Ian Yates SSCoach Points: 19738 More actions · Answer 2

That was going to be my suggestion, but he did say that they tried it in his post so we can cross that off

What are the properties of your linked server? (ie, in EM, what properties are ticked/unticked)

peterhe SSChampion Points: 11469 More actions · Answer 3

If more than servers are involved in a tran, sql server will automatically escalate the tran to a distributed tarn. So BEGIN DISTRIBUTED TRAN is the same as BEGIN TRAN in this senario

peterhe SSChampion Points: 11469 More actions · Answer 4

Whenever you COMMIT or ROLLBACK a tran, you need to test @@TRANCOUNT. If @@TRANCOUNT>0 do it, otherwise don't do it. This will avoid the erro "that the code has an extra "commit tran".

The reason is for nested trans, rollback command in any level will rollback the whole tran.

Another thing is that you need to test the @@error after each statement in the tran and the return value of the nested SP (it's better that the SP returns a value) for any errors. If error happens, GOTO ROLLBACK_EXIT statement.

ROLLBACK_EXIT:

IF @@TRANCOUNT>0 ROLLBACK