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 error in distributed transaction Expand / Collapse
Author
Message
Posted Friday, October 22, 2010 9:28 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, September 13, 2014 11:32 PM
Points: 1,862, Visits: 3,606
Can anyone help me with this?

I have checked all my settings according to link http://www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp, and have restarted the MSDTC and SQL on both the local and remote servers, and yet this linked server query is still returning error below:

BEGIN DISTRIBUTED TRANSACTION
SELECT TOP 1 col1 FROM LinkedServer1.db1.dbo.tbl1
ROLLBACK TRAN

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


I'm suspecting this may be caused by the fact that the local server is on a TEST domain, while the remote server is on a production domain, and there is one-way trust between the 2 domains, ie. one can only go from prod to test with Windows authentication and not the other way around.

However, I have configured the linked server to use SQL auth with a sql login on the remote server, so I am not sure if this is indeed the issue.

Any insights would be appreciated.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1009296
Posted Friday, October 22, 2010 9:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 5,019, Visits: 10,559
Can the two DTC connect to each other?
Have you checked that with DTCPing?


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1009324
Posted Friday, October 22, 2010 11:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, September 13, 2014 11:32 PM
Points: 1,862, Visits: 3,606
Gianluca Sartori (10/22/2010)
Can the two DTC connect to each other?
Have you checked that with DTCPing?


Got following error on local server when running DTCping:


++++++++++++Start DTC Binding Test +++++++++++++
Trying Bind to RemoteSrvr1
10-22, 12:57:09.192-->LocalSrvr1 Initiating DTC Binding Test....
Test Guid:FA12FEB1-8D3D-4297-B8F2-3A71341D788A
Binding call to RemoteSrvr1 Failed
In GUID
Out GUID
Session Down


It looks like it is indeed a domain issue.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1009374
Posted Wednesday, October 27, 2010 10:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 1:08 AM
Points: 102, Visits: 416
Pls check SPN settings
Post #1011709
Posted Wednesday, October 27, 2010 12:44 PM


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 @ 1:00 PM
Points: 514, Visits: 1,732
Don't forget that MSDTC is DNS based. I have found that a quick entry into the host file will fix these cross domain issues pretty quick...

so on your source server, add a host file entry to the target, on the target, enter a host record for the source.

then try again.

host file is always here;
C:\WINDOWS\system32\drivers\etc\hosts
Post #1011815
Posted Wednesday, October 27, 2010 12:53 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, September 13, 2014 11:32 PM
Points: 1,862, Visits: 3,606
Thanks both for your responses.

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1011822
Posted Wednesday, October 27, 2010 12:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 10:03 AM
Points: 34, Visits: 644
Can be done at Active Directory Domain Trust. Both domains need have trust and and an exception with IP address. Please look for the article called:
"Create a two-way, external trust for both sides of the trust"

Thanks.
Post #1011825
Posted Thursday, November 4, 2010 11:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, September 13, 2014 11:32 PM
Points: 1,862, Visits: 3,606
I just found out that on the remote server, the MSDTC setting is on "Mutual Authentication Required"; it should be on "No Authentication Required".

I will change it and restart the MSDTC and SQL services, and see if that fixes the issue:

begin distributed transaction
select * from LinkedSrvrName.dbName.dbo.tblName
rollback tran

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


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1016046
Posted Thursday, January 24, 2013 5:35 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: Today @ 6:41 AM
Points: 506, Visits: 540
Nothing worked except the below mentioned solution.

Open command prompt as Administrator and enter the following commands

C:\> net stop msdtc

C:\> msdtc -uninstall

C:\> msdtc -install

followed by server restart..

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/eb4eda65-1759-4266-a304-1f34c4b93680/

http://36chambers.wordpress.com/2011/04/08/msdtc-no-transaction-is-active/

Post #1411083
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse