SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Beratung
Beratung
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 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
Beratung
Beratung
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 34
Any help with this would be greatly appreciated!
adrianw
adrianw
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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 Smile
Jacob Luebbers
Jacob Luebbers
SSChasing Mays
SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)

Group: General Forum Members
Points: 604 Visits: 1215
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
adrianw
adrianw
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 22
We opened up a microsoft support query, and I got it resolved. BigGrin
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! Smile
Jacob Luebbers
Jacob Luebbers
SSChasing Mays
SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)

Group: General Forum Members
Points: 604 Visits: 1215
Good to hear!

Regards,

Jacob
petedoyon
petedoyon
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 58
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
guizonatto
guizonatto
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 83
Pete,

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

Best regards,

Guilherme Zonatto.
Mohan Kumar
Mohan  Kumar
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1557 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
jmracine
jmracine
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 148
Thanks Guys, you have saved me a lot of trouble!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search