So I went through all the help I could google but my problem persists.
I have server A SQL 2008 R2 and server B SQL 2005. they are on different networks to be clear. So i have this linked server. all set up. But when I try DT like f.e.
OLE DB provider "SQLNCLI10" for linked server "B" 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 "B" was unable to begin a distributed transaction.
I set another SQL 2005 which is on the same network and the same statement works. To confuse even more I got Oracle on the other network and the DT to the oracle works as well. At the same time Admin in the other company did similar
tests and he can confir that within the network DT works. The firewall- hmm honestly it doesn;t exist between the 2 networks anymore as we put one big hole for all the traffics between the 2 networks.
By saying the servers are on different networks I mean there is company A and company B with their own domains.
Is there a tool like dtcping which will work form machines on different networks/domains?
First, please make sure the linked server (SQL Server 2005 on different network) connection works:
1. Find the linked server under “Linked Server” object.
2. Right click the linked server name and click “Test Connection”.
If the issue persists when linked server connection works, let’s refer to the steps on the following documents to troubleshoot the issue:
FIX: Error message when you run a distributed query in SQL Server 2005 or SQL Server 2008: "OLE DB provider 'SQLNCLI' for linked server '<Linked Server>' returned message 'No transaction is active'": http://support.microsoft.com/kb/954950