posted
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.
Posts: 172 | Registered: Aug 2001
| IP: Logged |
posted
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?
Posts: 172 | Registered: Aug 2001
| IP: Logged |
posted
Although it's been a while, I'm pretty sure XACT_ABORT is required for all data change transactions between OLEDB providers.
Posts: 37449 | Registered: May 1999
| IP: Logged |
Printer-friendly view of this topic
