Advertisements

Archive

Posts Tagged ‘MSDTC’

Inserting into Linked Servers

March 2, 2009 Leave a comment

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:

http://blogs.msdn.com/distributedservices/archive/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool.aspx

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.

Advertisements
%d bloggers like this: