Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

OLE DB provider "SQLNCLI" for linked server " " returned message "No transaction is active." Expand / Collapse
Author
Message
Posted Friday, October 26, 2007 5:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 8:15 AM
Points: 32, Visits: 34
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
Post #415654
Posted Tuesday, October 30, 2007 8:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 8:15 AM
Points: 32, Visits: 34
Any help with this would be greatly appreciated!
Post #416540
Posted Sunday, April 20, 2008 12:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2008 3:16 AM
Points: 2, Visits: 22
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 :)
Post #487654
Posted Tuesday, April 22, 2008 6:58 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 6:13 PM
Points: 318, Visits: 1,183
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
Post #489003
Posted Wednesday, April 23, 2008 8:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2008 3:16 AM
Points: 2, Visits: 22
We opened up a microsoft support query, and I got it resolved. :D
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! :)
Post #489348
Posted Wednesday, April 23, 2008 6:03 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 6:13 PM
Points: 318, Visits: 1,183
Good to hear!

Regards,

Jacob
Post #489646
Posted Tuesday, April 29, 2008 5:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 9:46 AM
Points: 6, Visits: 55
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
Post #492000
Posted Monday, December 7, 2009 12:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 10:44 AM
Points: 19, Visits: 83
Pete,

Did you have this issue solved? I am facing the same problem that you are.

Best regards,

Guilherme Zonatto.
Post #830153
Posted Thursday, May 27, 2010 5:57 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 5:36 PM
Points: 1,339, Visits: 596
I am sure you might have resolved issue by now but just in case Not resolved then you may refer link

www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp

that can guide you resolve this issue even for Windows Server 2008.


--www.sqlvillage.com
Post #929385
Posted Wednesday, November 24, 2010 2:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 21, 2011 5:34 PM
Points: 11, Visits: 148
Thanks Guys, you have saved me a lot of trouble!
Post #1026189
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse