Recently I was trying to help someone on Microsoft Newsgroups with issue with Linked Server. When inserting into Linked Server directly via the SQL Statement from a Query analyzer there were no issues everything worked successfully.
But when they changed the insert to work from with in a trigger the application started failing with following error:
Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider ‘SQLOLEDB’ was unable to begin a distributed transaction.
Their SQL Server configuration:
- Two physical SQL Servers.
- In two different domains.
- With firewall between them; all relevant ports for DTC opened for communication.
I suggested checking the settings for her DTC security and configuration as in the following site:
http://bytes.com/topic/sql-server/answers/513448-trigger-between-linked-server-dtc-issue
But it did not help them out, they decided to use DTCPing.exe (Download Here) utility to further troubleshoot the issue and this time following error showed up:
————————————–
…
tablename= #dtc28215
Creating Temp Table for Testing: #dtc28215
Warning: No Columns in Result Set From Executing: ‘create table #dtc28215 (ivalint)’
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Error:
SQLSTATE=25S12,Native error=0,msg='[Microsoft][SQL Native Client]Die Transaktion wurde bereits implizit oder explizit ³bertragen oder abgebrochen.’
Error:
SQLSTATE=24000,Native error=0,msg=[Microsoft][SQL Native Client]Ung³ltiger Cursorstatus
Typical Errors in DTC Output When
a. Firewall Has Ports Closed
-OR-
b. Bad WINS/DNS entries
-OR-
c. Misconfigured network
-OR-
d. Misconfigured SQL Server machine that has multiple netcards.
Aborting DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer.
————————————–
(a-c) There is a firewall and the servers are in two different domains, but name resolution (ping and nslookup) with full name . work correct on both sides. In Firewall all ports are opened in both sides.
(d) The SQL server has 2 network cards, but the second is disabled. So this might not be the problem.
I had seen similar DTCPing errors before so I told them to reference the Microsoft Tech reference link to see if this will help out:
They ended up opening a call and following is the solution they came up with this problem:
- DTC Uses NetBIOS when communicating between servers.
- So adding each server to HOSTS file on each of the two SQL Servers at \driver\etc\host resolved the issue.
I have not had to do that any of the configurations myself; but their issue was communication between two servers on two different domains.
To read the original Microsoft Newsgroup Posting, please click here.
SQL Canada (SQLCAN) 