July 18, 2007 at 11:07 am
Hello,
I have two servers each with SQL Server 2005 database installed and let's call them as Server A and B. We have a linked server connection configured on Server A to Server B and that works. We call a stored procedure on the database which is on Server A.
It then suppose to start a transaction (Begin Transaction) and insert record into one table and then call a stored procedure which is on Server B database through linked server connection. I would like to Commit only if the remote stored procedure call executed successfully othwise rollback.
However it can't successfully call the remote stored procedure if I keep Begin Transaction. I have to comment that line out for this to work. The problem is if something is wrong on Server B my transaction is getting inserted on Server A which I don't want to happen.
Is there any way that I could achieve this?
Any help would be greatly appreciated.
Satish
July 18, 2007 at 12:11 pm
try BEGIN DISTRIBUTED TRANSACTION!
* Noel
July 19, 2007 at 3:12 am
with the distribution transaction you will also need to specify SET TRANSACTION ISOLATION LEVEL SERIALIZABLE else it will result in a error.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply