Getting Distributed Transaction Errors - But why?

Posted on 2014-01-13
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 167 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


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 333 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 333 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query execution hang 5 29
Sql server insert 13 29
Query Help - MSSQL - Averages 5 27
VB.NET 2008 - SQL Timeout 9 24
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

776 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