October 26, 2007 at 5:26 pm
I have linked a local SQL 2005 Standard Server 32-bit Unclustered to a remote SQL Server 2005 Standard 64-bit Clustered Server. I am able to run a Stored Procedure that is on the remoted clustered from the Local Unclustered server and have the results grid populate with the select statement's results that is within the stored procedure.
When I try to run the same stored procedure on the Local and insert it into a Local table I get the following error:
OLE DB provider "SQLNCLI" for linked server " " returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 40
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "" " was unable to begin a distributed transaction.
I have seen a lot of post in various forums and on MSDN and have addressed all issues with com services settings and the like, but to no avail. Anyone else have any other suggestions?
Thanks,
DP
October 30, 2007 at 8:27 am
Any help with this would be greatly appreciated!
April 20, 2008 at 12:51 pm
well I am getting exactly the same behavior, and I have the identical environment to you - will be interesting to see what the gurus have to say 🙂
April 22, 2008 at 6:58 pm
How exactly are you running the remote SP? You will need to ensure that your MSDTC setup is working properly if you want to insert the results of the remote proc into a local table. Check out this TechNet article for generic troubleshooting steps: http://support.microsoft.com/default.aspx?scid=kb;en-us;306212
If your local batch is doing any kind of data modification operation (including INSERT of any form) there will be an implicit local transaction, which will be automatically promoted to a distributed transaction and thus require MSDTC to be working properly. Your SET XACT_ABORT setting will probably be relevant here too.
Regards,
Jacob
April 23, 2008 at 8:53 am
We opened up a microsoft support query, and I got it resolved. 😀
It seems that there can be numerous causes for this error, but in my case,
even though I had DTCPing working bi-directionally, the DTC security settings
were set to use mutual authentication.
This only works if both servers are on the same domain, and ours are in separate DMZ's,
so I went through the following steps on all machines participating
in the distributed transaction to get it working:
1. open up "Component Services"
Control Panel > Administrative tools > Component Services
2. in Component Servies, right click "My Computer" and select "Properties"
Console Root > Component Services > Computers > My Computer
3. select the "MSDTC" tab, and click "Security Configuration" in the "Transaction Configuration" group box
4. in "Security Settings" > "Transaction Manager Communication"
select "No Authentication Required"
5. restart the DTC service (should do so automatically)
Originally, I had "Mutual Authentication Required" selected.
I dont know if the "Incoming Caller Authentication Required" option would work.
I hope that this helps some of you! 🙂
April 23, 2008 at 6:03 pm
Good to hear!
Regards,
Jacob
April 29, 2008 at 5:49 am
We've also been sporadically experiencing this same error. The calling server is already set to "No Authentication Required" and the other is still on WIN2K server. Where is this MSDTC setting in WIN2K?????
Pistol Pete
December 7, 2009 at 12:06 pm
Pete,
Did you have this issue solved? I am facing the same problem that you are.
Best regards,
Guilherme Zonatto.
May 27, 2010 at 5:57 pm
I am sure you might have resolved issue by now but just in case Not resolved then you may refer link
that can guide you resolve this issue even for Windows Server 2008.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
November 24, 2010 at 2:07 pm
Thanks Guys, you have saved me a lot of trouble!
March 8, 2011 at 7:11 am
I am getting the same error even I did the same setting in both of my server specified in the link http://www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp
March 8, 2011 at 7:15 am
I am getting the same error even after I did the setting mentioned in the site. can any one suggest
March 31, 2011 at 8:02 am
Like most other users I'm too lazy to read through the entire comment thread. Can someone please spoonfeed me the answer.
May 23, 2011 at 8:55 am
Hi all,
I've been getting the same error message trying to fill a local table variable with the results of a remote stored procedure. Local database is SS2008 R2 on Win2008, and remote is SS2005 on Win2003 and in a different (trusted) domain.
I set XACT_ABORT to off; and that didn't resolve it.
I followed the instructions in the linked articles and set DTC security to "No Authentication Required"; and that didn't resolve it.
I tested the linked server by running a direct query against a test table; and that ran fine. So there isn't a problem with the network, linked server settings, or security between the domains. It just seems to be with the INSERT INTO.... EXEC part.
As a workaround I have re-written part of the remote sproc to fill an output varchar parameter with the same result set but using FOR XML. I then shred the xml in the local sproc. This seems to solve my immediate problem.
Thanks for the above links anyway! They helped 🙂
John
January 12, 2012 at 2:55 am
This change of setting on SQL2008 R2 works for me.
Thanks for sharing this information.
Thanks
Amit
Regards,
Amit[font="Arial"][/font]
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply