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

Question

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

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

goiskul SSC Rookie Points: 25 More actions · Answer 1

Thanks guys, After along long journey in google.

adrianw (4/23/2008)
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! 🙂

pkennedy 77296 SSC Journeyman Points: 76 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sivashankar-350147 SSC Enthusiast Points: 164 More actions · Answer 5

sivashankar-350147

SSC Enthusiast

Points: 164

August 18, 2014 at 6:40 am

#1738832

Thanks Mohan Kumar, works well. Saved my time.

karthik babu Hall of Fame Points: 3335 More actions · Answer 6

Hi All,

I got the below error while attempting to run a distributed transaction. The two servers are in different workgroup. We dont have a domain. Please let me know the solution.

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

Msg 7391, Level 16, State 2, Line 3

The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "SERVERDNSNAME" was unable to begin a distributed transaction.

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂