August 23, 2005 at 2:20 am
I have 2 SQL servers. Server A and B. I have linked server B to A through the sp_addLinkedServer command.
I am able to access the data from Server B from Server A. I am even able to fire stored procedures.
My problem comes when I have a Stored procedure on server B which has an insert/Update with in a transaction. This procedure is called within another procedure from Server A which is also with in a transaction. In such a scenario if there is a rollback on the Server B, then I get an error and I cant do much. Consider the following example
On SERVER B
create proc a as
begin tran
update master..table1 set x = y
rollback tran
On Server A I am calling it like this
begin tran
exec [SERVER_B].master.dbo.a
rollback tran
Is there any solution to this?
Hoping for a reply.
Thanks
PG
August 23, 2005 at 10:16 am
In this case MSDTC must be running. And in both your SP, you need to have ANSI_NULLS set to ON, and XACT_ABORT set to ON.
Can you post the error you got?
August 24, 2005 at 11:47 pm
I have MSDTC On on both the servers. the ANSI NULLS and XACT ABORT is also ON
It works fine if on the linked server the transaction is being committed. For eg.
Begin Tran [Local Server]
Call procedure on Linked server which has a transaction and it is being committed
Rollback or Commit tran [Local Server]
However if the transaction gets rolled back on the Linked server then I get an error. In this case I am also not able to get the return status of the procedure. The errors which I get are as follows.
Server: Msg 8525, Level 16, State 1, Line 8
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Server: Msg 7221, Level 18, State 2, Line 8
Could not relay results of procedure 'a' from remote server 'ORCHID\RETAIL'.
The transaction active in this session has been committed or aborted by another session.
Any Clues?
Thanks
August 25, 2005 at 7:12 am
This is the expected behaviour.
If a tran gets rolled back on the linked server, the MSDTC rolls back all involved local transactions. The return value of the remote SP is unavailable.
Can you check the @@TRANCOUNT before rolling back the transaction in your SP?
August 25, 2005 at 11:04 pm
OK, I can check the @@trancount and do a selective rollback. But why do I get the errors which I do. Also I need to call the procedure on the linked server as follows
exec @return = [ORCHID\RETAIL].master.dbo.a
This is because I need to know the return status of the procedure in many cases.
What other method can i employ. Please help
August 26, 2005 at 7:54 am
Check @@error first after you make the call.
If no error and @@TRANCOUNT>0, commit it and you can get the value of @return.
If @@error<>0 and @@TRANCOUNT>0, roll it back. In this case the return value (@return) is not available.
August 26, 2005 at 4:53 pm
make sure RPC, RPC Out is on. Check this under Server Option of ur linked Server Name
[font="Verdana"]--www.sqlvillage.com[/size][/font]
August 28, 2005 at 11:17 pm
The RPC OUT is on.
Even If I check the value of @@error (which by the way is 7221) the error statements come before that, so I dont have any way to supress those statements.
Any way out??
August 29, 2005 at 9:43 pm
Here is the contradiction. For linked server, th SP needs to set XACT_ABORT ON. It means the SP will rollback and termidate immediately if run time errors happen. The application server (your db client) perhaps is the best place to handle this kind of error.
It's better to do all possible data validation before starting a transaction. So there is little chance that the transaction fails. And if it does, the application server can still handle it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply