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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now


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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

630 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