Solved

Getting Distributed Transaction Errors - But why?

Posted on 2014-01-13
10
495 Views
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).
0
Comment
Question by:Roxanne25
  • 5
  • 4
10 Comments
 
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

but

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.
0
 

Author Comment

by:Roxanne25
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.
0
 
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
0
 

Author Comment

by:Roxanne25
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
begin
insert into vw_tablename
select * from local table
where bla bla
end

set commands were only this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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.
0
 
LVL 75

Accepted Solution

by:
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Roxanne25
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.
0
 
LVL 75

Expert Comment

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

Author Comment

by:Roxanne25
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?
0
 
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.
0
 

Author Comment

by:Roxanne25
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!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now