Here is my problem. I have a database on my local machine. Part of what I need to do is connect this database with another database across the internet.
I can set up the linked server fine, and it connects all right. I can, for example execute SELECT * FROM Linked_Server_Name.Database_Name.dbo.TableName and I get everything from TableName. I can also perform an INSERT INTO statement, and it works correctly.
The problem I run into, however, is that when this code is in a Trigger. I put PRINT statements to see where it crashes, and it first does a SELECT just fine, but afterwards it does an INSERT, and that is where it fails. (executing the exact same INSERT statement outside the trigger works perfectly.)
The error message I get is: Msg 7391, Level 16, State 2, Procedure tr_InsertBilling, Line 58 The operation could not be performed because OLE DB provider "MSDASQL" for linked server "TITAN_LAW" was unable to begin a distributed transaction.
Any help will be much appreciated. Thank You.
Posted by TomDavidson (Member # 124) on :
Try starting a simple SELECT stored procedure with "BEGIN TRANSACTION." Does this cause the error to occur?
Is the DTC or MSDTC service active on both servers?
Are you setting XACT_ABORT ON?
Posted by Slim (Member # 2334) on :
Okay, I wrote a stored procedure using "BEGIN TRANSACTION" that only contained a select statement from the linked server. That worked fine. I tried it again, changing the SELECT to an INSERT and it failed.
DTC is on at my end. I don't know about the other end, but I assume it is, because the other students projects were able to work that also used linked servers. Unless there's something specific about my database hosted at the school computer (that I am linking to) the problem shouldn't be at that end.
I am not setting XACT_ABORT ON. I read something about that, but I wasn't able to really tell how/where to use it. Is that my problem?
Posted by TomDavidson (Member # 124) on :
Although it's been a while, I'm pretty sure XACT_ABORT is required for all data change transactions between OLEDB providers.
Posted by Slim (Member # 2334) on :
After what you said, I tried looking up how to use XACT_ABORT, but nothing was very helpful on how to use it.
I tried adding SET XACT_ABORT ON in various places in that test stored procedure I wrote, but nothing made a difference.
Either I'm using it wrong, or there is still something else that is the problem. ...
Posted by TomDavidson (Member # 124) on :