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

Linked Server Distributed transaction Error Expand / Collapse
Author
Message
Posted Friday, September 13, 2013 2:07 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 586, Visits: 591
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.
Post #1494498
Posted Friday, September 13, 2013 10:26 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 23, 2014 9:00 PM
Points: 611, Visits: 448
Does your other transaction or SP working fine with same linked server .
Post #1494655
Posted Friday, September 13, 2013 10:49 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 586, Visits: 591
No it is not.
I have also tried to execute other sp using same linked server but it is giving me same error.
Post #1494667
Posted Friday, September 13, 2013 10:59 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 586, Visits: 591
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
Post #1494672
Posted Friday, September 13, 2013 3:33 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 817, Visits: 743
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
Post #1494747
Posted Tuesday, September 17, 2013 5:34 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 586, Visits: 591
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.

Post #1495452
Posted Tuesday, September 17, 2013 3:06 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 817, Visits: 743
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
Post #1495694
Posted Tuesday, September 17, 2013 4:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 1, 2014 1:35 AM
Points: 42, Visits: 399
Do you have RPC enabled for that linked server, server options?
Post #1495715
Posted Friday, September 20, 2013 2:26 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 586, Visits: 591
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.
Post #1496747
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse