OLE DB provider "SQLNCLI" for linked server " " returned message "No transaction is active."

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » OLE DB provider "SQLNCLI" for linked server "...

17 posts, Page 1 of 2

1 2 »»

OLE DB provider "SQLNCLI" for linked server " " returned message "No transaction is active."

Rate Topic

Display Mode

Topic Options

Author

Message

Beratung

Posted Friday, October 26, 2007 5:26 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 17, 2012 2:49 PM
Points: 32, Visits: 33

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

Beratung

Posted Tuesday, October 30, 2007 8:27 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 17, 2012 2:49 PM
Points: 32, Visits: 33

Any help with this would be greatly appreciated!

Post #416540

adrianw

Posted Sunday, April 20, 2008 12:51 PM

Forum 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

Jacob Luebbers

Posted Tuesday, April 22, 2008 6:58 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 5:42 PM
Points: 318, Visits: 1,173

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

adrianw

Posted Wednesday, April 23, 2008 8:53 AM

Forum 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

Jacob Luebbers

Posted Wednesday, April 23, 2008 6:03 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 5:42 PM
Points: 318, Visits: 1,173

Good to hear!

Regards,

Jacob

Post #489646

petedoyon

Posted Tuesday, April 29, 2008 5:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 4:40 AM
Points: 6, Visits: 53

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

guizonatto

Posted Monday, December 07, 2009 12:06 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 24, 2013 8:29 AM
Points: 19, Visits: 70

Pete,

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

Best regards,

Guilherme Zonatto.

Post #830153

Mohan Kumar

Posted Thursday, May 27, 2010 5:57 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 03, 2013 4:44 PM
Points: 1,336, Visits: 595

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

jmracine

Posted Wednesday, November 24, 2010 2:07 PM

Grasshopper

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 »

17 posts, Page 1 of 2

1 2 »»

Permissions