March 14, 2017 at 7:39 am
Hi Experts,
In our production server, we are using one query to fetch the result from linked server, using one particular column in linked server database.(Eg: column name tunnel, it has multiple rows with names DAL, VB, AUS, SAO we can able to fetch the data for all rows expect SAO). Whether the problem with query or Linked server. Below is the error we faced.
Msg 7391, Level 16, State 2, Line 17
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "SAO" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI11" for linked server "SAO" returned message "No transaction is active.".
Msg 3930, Level 16, State 1, Line 98
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Please help on this.
Thanks
Naren M
March 14, 2017 at 9:21 am
Narine M - Tuesday, March 14, 2017 7:39 AMHi Experts,In our production server, we are using one query to fetch the result from linked server, using one particular column in linked server database.(Eg: column name tunnel, it has multiple rows with names DAL, VB, AUS, SAO we can able to fetch the data for all rows expect SAO). Whether the problem with query or Linked server. Below is the error we faced.
Msg 7391, Level 16, State 2, Line 17
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "SAO" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI11" for linked server "SAO" returned message "No transaction is active.".
Msg 3930, Level 16, State 1, Line 98
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.Please help on this.
Thanks
Naren M
Can you share the query?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2017 at 10:45 am
Narine M - Tuesday, March 14, 2017 7:39 AMHi Experts,In our production server, we are using one query to fetch the result from linked server, using one particular column in linked server database.(Eg: column name tunnel, it has multiple rows with names DAL, VB, AUS, SAO we can able to fetch the data for all rows expect SAO). Whether the problem with query or Linked server. Below is the error we faced.
Msg 7391, Level 16, State 2, Line 17
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "SAO" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI11" for linked server "SAO" returned message "No transaction is active.".
Msg 3930, Level 16, State 1, Line 98
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.Please help on this.
Thanks
Naren M
What I would try is browsing through the linked server in SSMS for SAO and try selecting data from 1 table and see what you get. If you are still getting issues, then my next guess is your DTC is screwed up on that server.
I'd first verify that you have DTC configured properly on the server hosting SAO, and if you are confident that it is configured correctly, I'd check the windows error log on the server hosting SAO. Depending on what the event log says, you may need to blow away DTC and reinstall it.
This website shows somebody having a similar problem and they were able to resolve it by reinstalling DTC:
http://www.sqldbadiaries.com/2014/09/24/no-transaction-is-active-message-when-accessing-linked-server/
NOTE - I have not tested anything from that site nor did I write that, I just found it from a random bing search. It requires 2 reboots of the server so if this is a live production server, you likely want to schedule downtime unless this is causing the company to grind to a halt. Then you may want to try anything you can find online.
The only additions to the fixing steps on that site that I would recommend is to verify the DTC config after reinstalling it. They unfortunately do not reconfigure DTC after reinstalling it and since they blow out the registry related to DTC before reinstalling it, the config would be lost. I'd also back up the registry before blowing any keys away.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 14, 2017 at 12:35 pm
It has been a while but when I did experience this type of problem it usually turned out to be misconfigured Distributed Transaction Manager on one or both servers. This is outside of SQL Server, at the OS level.
Check the configurations.
March 14, 2017 at 12:41 pm
The SAO server is a AWS server..
March 14, 2017 at 1:31 pm
Narine M - Tuesday, March 14, 2017 12:41 PMThe SAO server is a AWS server..
There is the problem. From the documentation.
Amazon RDS currently does not support the following SQL Server features:
Distributed Queries (i.e., Linked Servers)
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 15, 2017 at 5:33 am
Hi Experts, the AUS database also in AWS server only, but it is working in that.
March 15, 2017 at 9:14 am
It would probably be handy if we had:
1 - the code being run that is failing
2 - the linked server connection information
I was re-reading the original question and I am a little confused about when you say that a certain row fails to return and I think seeing the full query would help.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 16, 2017 at 2:07 am
The query is working fine in all servers, expect SAO server.. Checked the linked connections using the simple select query it's working fine...in that query it fetches the result from SAO server, through linked server connections...But it error outs...
March 16, 2017 at 8:02 am
And what exactly is the error again?
March 16, 2017 at 8:11 am
Narine M - Thursday, March 16, 2017 2:07 AMThe query is working fine in all servers, expect SAO server.. Checked the linked connections using the simple select query it's working fine...in that query it fetches the result from SAO server, through linked server connections...But it error outs...
did you look at that link I posted?
Also, did you compare the linked server configuration for SAO and one of the working ones? The error I believe is indicating that your DTC is configured incorrectly for SAO.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 16, 2017 at 8:20 am
bmg002 - Thursday, March 16, 2017 8:11 AMNarine M - Thursday, March 16, 2017 2:07 AMThe query is working fine in all servers, expect SAO server.. Checked the linked connections using the simple select query it's working fine...in that query it fetches the result from SAO server, through linked server connections...But it error outs...did you look at that link I posted?
Also, did you compare the linked server configuration for SAO and one of the working ones? The error I believe is indicating that your DTC is configured incorrectly for SAO.
I have to agree with bmg. Any time I had issues with distributed transaction errors it always came down to how DTC was configured.
March 16, 2017 at 11:05 am
Lynn Pettis - Thursday, March 16, 2017 8:19 AMbmg002 - Thursday, March 16, 2017 8:11 AMNarine M - Thursday, March 16, 2017 2:07 AMThe query is working fine in all servers, expect SAO server.. Checked the linked connections using the simple select query it's working fine...in that query it fetches the result from SAO server, through linked server connections...But it error outs...did you look at that link I posted?
Also, did you compare the linked server configuration for SAO and one of the working ones? The error I believe is indicating that your DTC is configured incorrectly for SAO.
I have to agree with bmg. Any time I had issues with distributed transaction errors it always came down to how DTC was configured.
Lynn Pettis - Thursday, March 16, 2017 8:19 AMbmg002 - Thursday, March 16, 2017 8:11 AMNarine M - Thursday, March 16, 2017 2:07 AMThe query is working fine in all servers, expect SAO server.. Checked the linked connections using the simple select query it's working fine...in that query it fetches the result from SAO server, through linked server connections...But it error outs...did you look at that link I posted?
Also, did you compare the linked server configuration for SAO and one of the working ones? The error I believe is indicating that your DTC is configured incorrectly for SAO.
I have to agree with bmg. Any time I had issues with distributed transaction errors it always came down to how DTC was configured.
Lynn Pettis - Thursday, March 16, 2017 8:19 AMbmg002 - Thursday, March 16, 2017 8:11 AMNarine M - Thursday, March 16, 2017 2:07 AMThe query is working fine in all servers, expect SAO server.. Checked the linked connections using the simple select query it's working fine...in that query it fetches the result from SAO server, through linked server connections...But it error outs...did you look at that link I posted?
Also, did you compare the linked server configuration for SAO and one of the working ones? The error I believe is indicating that your DTC is configured incorrectly for SAO.
I have to agree with bmg. Any time I had issues with distributed transaction errors it always came down to how DTC was configured.
Lynn Pettis - Thursday, March 16, 2017 8:19 AMbmg002 - Thursday, March 16, 2017 8:11 AMNarine M - Thursday, March 16, 2017 2:07 AMThe query is working fine in all servers, expect SAO server.. Checked the linked connections using the simple select query it's working fine...in that query it fetches the result from SAO server, through linked server connections...But it error outs...did you look at that link I posted?
Also, did you compare the linked server configuration for SAO and one of the working ones? The error I believe is indicating that your DTC is configured incorrectly for SAO.
I have to agree with bmg. Any time I had issues with distributed transaction errors it always came down to how DTC was configured.
Lynn Pettis - Thursday, March 16, 2017 8:19 AMbmg002 - Thursday, March 16, 2017 8:11 AMNarine M - Thursday, March 16, 2017 2:07 AMThe query is working fine in all servers, expect SAO server.. Checked the linked connections using the simple select query it's working fine...in that query it fetches the result from SAO server, through linked server connections...But it error outs...did you look at that link I posted?
Also, did you compare the linked server configuration for SAO and one of the working ones? The error I believe is indicating that your DTC is configured incorrectly for SAO.
I have to agree with bmg. Any time I had issues with distributed transaction errors it always came down to how DTC was configured.
Lynn Pettis - Thursday, March 16, 2017 8:19 AMbmg002 - Thursday, March 16, 2017 8:11 AMNarine M - Thursday, March 16, 2017 2:07 AMThe query is working fine in all servers, expect SAO server.. Checked the linked connections using the simple select query it's working fine...in that query it fetches the result from SAO server, through linked server connections...But it error outs...did you look at that link I posted?
Also, did you compare the linked server configuration for SAO and one of the working ones? The error I believe is indicating that your DTC is configured incorrectly for SAO.
I have to agree with bmg. Any time I had issues with distributed transaction errors it always came down to how DTC was configured.
Lynn Pettis - Thursday, March 16, 2017 8:19 AMbmg002 - Thursday, March 16, 2017 8:11 AMNarine M - Thursday, March 16, 2017 2:07 AMThe query is working fine in all servers, expect SAO server.. Checked the linked connections using the simple select query it's working fine...in that query it fetches the result from SAO server, through linked server connections...But it error outs...did you look at that link I posted?
Also, did you compare the linked server configuration for SAO and one of the working ones? The error I believe is indicating that your DTC is configured incorrectly for SAO.
I have to agree with bmg. Any time I had issues with distributed transaction errors it always came down to how DTC was configured.
In dtc , we have made the necessary changes llike allowing inbound, out bound and remote calls etc. It worked for AUS server but for SAO it didn't worked. Whether we need to change any thing in amazon security side.
March 16, 2017 at 11:09 am
bmg002 - Thursday, March 16, 2017 8:11 AMNarine M - Thursday, March 16, 2017 2:07 AMThe query is working fine in all servers, expect SAO server.. Checked the linked connections using the simple select query it's working fine...in that query it fetches the result from SAO server, through linked server connections...But it error outs...did you look at that link I posted?
Also, did you compare the linked server configuration for SAO and one of the working ones? The error I believe is indicating that your DTC is configured incorrectly for SAO.
Yes, i looked in to the post, whether the uninstallation and reinstallation of DTC services can be done by dba or should be done by it- admin
March 16, 2017 at 11:20 am
I can't help you with AWS. Have you tried contacting them to see if they can help?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply