Linked server error in distributed transaction

Question

Post reply

Linked server error in distributed transaction

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
October 22, 2010 at 9:28 am

#230780

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.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 9 posts - 1 through 9 (of 9 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Can the two DTC connect to each other?

Have you checked that with DTCPing?

-- Gianluca Sartori

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

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.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Akkare SSCrazy Points: 2848 More actions · Answer 3

Akkare

SSCrazy

Points: 2848

October 27, 2010 at 10:26 am

#1243039

Pls check SPN settings

Geoff A SSChampion Points: 11511 More actions · Answer 4

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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 5

Thanks both for your responses.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Hotfix SSC-Addicted Points: 449 More actions · Answer 6

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.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 7

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.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

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

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/%5B/url%5D

http://36chambers.wordpress.com/2011/04/08/msdtc-no-transaction-is-active/%5B/url%5D

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

Linked server error in distributed transaction

Cookies on SQLServerCentral