Go Premium for a chance to win a PS4. Enter to Win


Getting Distributed Transaction Errors - But why?

Posted on 2014-01-13
Medium Priority
Last Modified: 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:

INSERT vw_Name
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).
Question by:Roxanne25
  • 5
  • 4
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 668 total points
ID: 39777971
ok let me explain

if transaction happens on more than one server then it is called a distributed transaction.

for example:

Start transaction A

Update localTable
insert into localTable
Delete LocalTable

Commit Tranaction A

Will be a local transaction


Start transaction B

Update localTable
insert into remotelTable
Delete LocalTable

Commit Tranaction B

will become a distributed transaction...

So, it doesn't matter if you are doing insert / update/ delete on the server, what matters as long as you are using views for the tables on the server or synonyms pointing to other server and you are trying to update them... then it will be distributed transaction..

if you transaction on top of a distributed transaction then it will become nested transaction.

Author Comment

ID: 39777991
Ok thanks, that really doesn't answer my question.

I wasn't really questioning if it was or wasn't a distributed transaction but more specifically does it treat inserts differently than update and deletes... cause I can't figure out why it wasn't working with just the insert procedure and not the other 2.

I fixed it and its working now... but I was just trying to understand the issue if I could. What other information can I provide to give you more help?

1) linked server is sql server 2000
2) our server is sql server 2008 R2
3) web server is windows 2008 and the procedure is being called from Cold Fusion on a user submitted event.
4) we are using views to the linked server tables (select * from servername.schema.table)
5) procedure is inserting to the views and 2 other procedures to do updates and deletes
6) only had the error on the insert procedure
7) procedure would actually work if you ran it from SQL server directly but only failed with those error messages if it was ran from the Cold Fusion server.
8) This all worked fine up till December.

Also, none of my code is with a BEGIN TRANSACTION block.  They are not coded as transactions.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39778141
I wasn't really questioning if it was or wasn't a distributed transaction
But in a sense you were:  See here for example:
I was a bit puzzled by this because we don't use distributed transactions (at least I don't specifically code them).
The fact that you did not know you were using distributed queries is important and needed to be pointed out.

But to answer your question, please post your complete INSERT Stored Procedure as well as  the SET commands used.

Also, please indicate what Service Pack you are on for SQL Server 2000

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 39778203
I was referring to the fact that I do not use them by saying "BEGIN DISTRIBUTED TRANSACTION".

As I work on a classified system, I cannot post my stored procedure.  But as I said, it is very simple:

create stored procedure blah as
insert into vw_tablename
select * from local table
where bla bla

set commands were only this:

Today, I added the SET XACT_ABORT ON (and off at the end) which is what ultimately fixed it.

And I don't know what service pack the SQL 2000 instance is running on this is an external system to our own.

Appreciate the responses.
LVL 75

Accepted Solution

Anthony Perkins earned 1332 total points
ID: 39778251
Today, I added the SET XACT_ABORT ON (and off at the end) which is what ultimately fixed it.
That is correct.  In order to use a distributed query with any update operation (that includes INSERTs) you must use SET XACT_ABORT ON.

And I don't know what service pack the SQL 2000 instance is running on this is an external system to our own.
If you managed to fix it, then you must have the correct SP.

Author Comment

ID: 39778256
But why did it work fine all this time and just decide it wanted to break today... AND I haven't done anything to the delete and update stored procedure and it is working fine as well.  Only the add required me to put the XACT ABORT thing.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39778260
I am afraid I have no idea.

Author Comment

ID: 39778262
Sniffles... that is what I wanted to know.  :(

Would it be a best practice and safe bet to assume that I should go ahead and put the xact_abort statement in the other procedures as well just to be safe?
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1332 total points
ID: 39778293
I would only do it if you need to.  If you say you don't have it set then it cannot be required.

Author Comment

ID: 39778313
Hmmm ok, I hate unsolved mysteries.  It just doesn't make any sense to me why this is a new problem on stuff that always worked fine before... grrr...

Thanks for the inputs!

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question