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 ««12

OLE DB provider "SQLNCLI" for linked server " " returned message "No transaction is active." Expand / Collapse
Author
Message
Posted Tuesday, March 8, 2011 7:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 10:25 PM
Points: 93, Visits: 94
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
Post #1074767
Posted Tuesday, March 8, 2011 7:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 10:25 PM
Points: 93, Visits: 94
I am getting the same error even after I did the setting mentioned in the site. can any one suggest
http://www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp
Post #1074771
Posted Thursday, March 31, 2011 8:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 1, 2011 11:25 AM
Points: 1, Visits: 1
Like most other users I'm too lazy to read through the entire comment thread. Can someone please spoonfeed me the answer.
Post #1086863
Posted Monday, May 23, 2011 8:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 7:49 AM
Points: 4, Visits: 29
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
Post #1113372
Posted Thursday, January 12, 2012 2:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 6, 2014 5:03 AM
Points: 3, Visits: 28
This change of setting on SQL2008 R2 works for me.
Thanks for sharing this information.
Thanks
Amit


Regards,
Amit[size="7"][/size]
Post #1234598
Posted Wednesday, February 1, 2012 6:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 8, 2012 1:30 AM
Points: 1, Visits: 6
Thanks guys, After along long journey in google.

adrianw (4/23/2008)
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 #1245017
Posted Wednesday, May 15, 2013 11:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 12:26 PM
Points: 2, Visits: 86
I had the same problem. My two servers were in different domains. I had to add hosts entries on each server. For example, I have server1.domain1.local and server2.domain2.local. On Server1, I added IP, server2 to hosts file. On Server2, I added IP, server1 to hosts file. This resolved my issue.
Post #1453238
Posted Monday, September 23, 2013 7:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 37,099, Visits: 31,649
pkennedy 77296 (5/15/2013)
I had the same problem. My two servers were in different domains. I had to add hosts entries on each server. For example, I have server1.domain1.local and server2.domain2.local. On Server1, I added IP, server2 to hosts file. On Server2, I added IP, server1 to hosts file. This resolved my issue.


I know this is an old post but that sounds a whole lot better than "No Authentication Required". Thanks for taking the time to post it.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497616
Posted Tuesday, September 24, 2013 10:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 37,099, Visits: 31,649
Actually, the resident "hack" genius, Mr. Erland Sommarskog, may have provided the solution in one of his many great blog entries. We're testing it out to see what, if anything, can go wrong but it appears to be working. I'll be back with the results in a couple of days.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497982
Posted Monday, August 18, 2014 6:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 18, 2014 6:38 AM
Points: 33, Visits: 79
Thanks Mohan Kumar, works well. Saved my time.
Post #1604416
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse