March 10, 2009 at 8:55 am
I am attempting to run a SQL transaction on two different servers. When I delete records in the first table, I need to delete corresponding records in a second table. This seems straightforward on one server but the cross-server transaction gives an error. Both databases are SQL Server 2005. Here is the SQL I am using and the error it generates.
SET XACT_ABORT ON ;
BEGIN TRANSACTION ;
BEGIN TRY
DELETE FROM SERVER_1.Database_1.dbo.Table_1
WHERE (ID = @ID) ;
DELETE FROM SERVER_2.Database_2.dbo.Table_2
WHERE (ID = @ID) ;
COMMIT TRANSACTION ;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION ;
END CATCH ;
----------------------------------------------------
(1 row(s) affected)
OLE DB provider "SQLNCLI" for linked server "SERVER_2" returned message "Cannot start more transactions on this session.".
(0 row(s) affected)
----------------------------------------------------
I tried setting SET XACT_ABORT ON before each statement and still received errors. Is there is a way around this?
March 10, 2009 at 4:25 pm
Try using:
BEGIN DISTRIBUTED TRANSACTION;
Instead of BEGIN TRANSACTION.
Note
1. MSDTC must be configured correctly on both servers for this to work.
2. Keep in mind that distributed transactions are expensive operations.
Good Luck
* Noel
March 11, 2009 at 12:10 am
I was working with someone on DTS and Linked server on MS newsgroup.. maybe this will help you, link.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 11, 2009 at 7:51 am
This solution worked perfectly. Thanks so much!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply