Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

Getting Distributed Transaction Errors - But why?

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
Roxanne25
Asked:
Roxanne25
  • 5
  • 4
3 Solutions
 
Surendra NathTechnology LeadCommented:
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
 
Roxanne25Author Commented:
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
 
Anthony PerkinsCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Roxanne25Author Commented:
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
 
Anthony PerkinsCommented:
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
 
Roxanne25Author Commented:
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
 
Anthony PerkinsCommented:
I am afraid I have no idea.
0
 
Roxanne25Author Commented:
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
 
Anthony PerkinsCommented:
I would only do it if you need to.  If you say you don't have it set then it cannot be required.
0
 
Roxanne25Author Commented:
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now