January 13, 2014 at 11:43 am
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).
Any insight is appreciated.
January 13, 2014 at 12:47 pm
Not sure why you're getting the error without seeing more about how the code, your servers, the connections between them are all configured. But, you are using distributed transactions because you're using a linked server. That's pretty much the definition of distributed. Personally, I try to avoid it. If you need to move data to another system, it's generally a safer bet to simply connect directly to that other system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2014 at 1:57 pm
Well wouldn't know any other way to "directly connect" other than the linked server.
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.
January 13, 2014 at 3:20 pm
A linked server isn't direct. It's a hop away from the server. Direct would be to open a connection to the second server from the app code. But that's a different issue.
Hmmm... If the view references more than one table you can't do an insert, but it sounds like you got around it, so it can't possibly be the issue. It still seems like there must be a difference in the settings in the query itself. Other than the issue with multiple tables, it should just work. There's nothing else unique about inserts to my knowledge. So... I'm back to settings, either connection settings on the linked server, ANSI settings within the query itself, connection settings from the app... One of those.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2014 at 3:49 pm
Right right, I get ya... for us, the "direct" part is doing it directly from database to database. But yea, different topic. 😉
No, the views are not using multiple tables... I even altered the procedure before I added the XACT ABORT option to not use the view at all and point to the table directly from the linked server server.schema.table but it still threw the error about the XACT ABORT being OFF.
The wierd part is that this just start occurring... we haven't altered these procedures. The only thing different is the web server itself which was upgraded from windows 2003 to windows 2008 in December (an entirely new server was built). I'm going to venture a guess that this has to be the cause? I checked the DTC service and made sure all the configurations were good on the server.
But yea, by fixing the DTC service and adding the XACT ABORT ON statement to my procedure, it resolved the issue... I was just hoping I'd figure out why.... doesn't amke any sense that the update and delete procs would work without all these adjustments but the insert wouldn't.:blink: AND! It would work perfectly fine if you ran the procedure in the database just not from the application on the web server.
March 2, 2015 at 2:37 pm
I'm seeing both of these errors. What did you do to configure DTC to fix the distributed transactions error?
March 4, 2015 at 7:30 pm
Sorry to hear that! I'd like to tell you that I remember but I honestly don't. 🙁
I did some research online for the error message and found some online resources that said what options should be on/off for the DTC in windows. I do remember that... what they were, I don't quite remember. I also had to make sure I had the XACT ABORT statements in my stored procedures.
We have also long since upgraded our SQL 2000 box to a SQL 2008 R2 on brand new hardware - so I actually think part of the problem was just a really old crappy server that had a lot of issues.
Sorry I can't be of more help. 🙁 But I would definitely recommend doing some google searches, that's basically how I resolved it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply