Getting Distributed Transaction Errors - But why?
Posted on 2014-01-13
Hi there! I have a problem that is sort of stumping me... I have been working on it all morning and I was able to fix it, but I don't understand how or why it was fixed... lol.
First, I was getting this error message from our Cold Fusion application front end when it was trying to execute one of our stored procedures:
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" was unable to begin a distributed transaction.
I was a bit puzzled by this because we don't use distributed transactions (at least I don't specifically code them). I did some research online and I found out how to modify the DTC component on the server to have the proper configurations.
Then, when trying again we got this error message:
Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server "SERVERNAME". A nested transaction was required because the XACT_ABORT option was set to OFF.
So, I was able to resolve that as well by changing that option in the stored procedure...
Now, there are 3 stored procedures - One does inserts; one does updates; and one does deletes.
The actions are being done to a view in a database on another server. The view definition uses a linked server.
The error was/is only happening on the INSERT stored procedure. So, I'm a little baffled as to why it only bombs on the insert stored procedure and not the others. They are all coded in the same fashion ....
Do distributed transactions work differently if its an insert vs. update or delete? Why is it all of the sudden treating these as distributed transactions when they aren't coded as such?
The code is very simple and looks just like this:
SELECT bla, bla2, bla3
FROM local table
WHERE bla bla
And again vw_Name would be a table on another server that we have via Linked Server. It is also a SQL Server (but its SQL 2000).