Advertisements
Home > SQL Errors, Troubleshooting > Inserting into Linked Servers

Inserting into Linked Servers

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
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: