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


Linked Server Distributed transaction Error


Linked Server Distributed transaction Error

Author
Message
Sushant Yadav
Sushant Yadav
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1878 Visits: 876
Hi,

I am trying to execute procedure through linked server ,
but after executing the same it is giving me following error.

OLE DB provider "SQLNCLI" for linked server "XYZ"
returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 18
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "XYZ" was unable to begin a distributed transaction.



I have already enabled remote inbound and remote outbound from MS DTC settings in component service.

For Info. I am using SQL Server 2005 enterprise edition and Windows 2003 enterprise on both the machines.
RatanDeep Saha
RatanDeep Saha
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2032 Visits: 702
Does your other transaction or SP working fine with same linked server .
Sushant Yadav
Sushant Yadav
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1878 Visits: 876
No it is not.
I have also tried to execute other sp using same linked server but it is giving me same error.
Sushant Yadav
Sushant Yadav
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1878 Visits: 876
I tried it like

Inser into #tmp
Exec linkedserver.db.dbo.mysp

Then it is giving me error but when I directly run following statement then it gives me output

linkedserver.db.dbo.mysp
Erland Sommarskog
Erland Sommarskog
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13386 Visits: 879
What sort of environment is this? Are both computers in the same domain? Do you only have a workgroup?

Getting MSDTC to work when you don't have the trust from a domain can be very difficult. I have been successful in my workgroup at home, by have the same name for the service accounts for the DTC service with the same password.

On SQL 2008 there is a server option to turn off promotion to disitributed transaction, but alas it is not available in SQL 2005.

By the way, this forum is intended for the most recent release of SQL Server, SQL 2014, currently in beta.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Sushant Yadav
Sushant Yadav
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1878 Visits: 876
I have one server in domain and another is in workgroup.

The one which is in Domain on that server i am executing the query and the procedure is on server which is in work group environment.
Erland Sommarskog
Erland Sommarskog
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13386 Visits: 879
That sounds indeed like a difficult case. I don't know enough about Windows to say whether this can be solved and how. And MSDTC is a Windows component.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
iaminyourhead
iaminyourhead
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 415
Do you have RPC enabled for that linked server, server options?
Sushant Yadav
Sushant Yadav
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1878 Visits: 876
Thanks for your reply,
My issue got solved ,I just updated my host file from drives\etc folder,
I added IP address and host name into the file in both server. :-):-):-)
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