Solved

Cross-Server MS SQL RPC call works but fails when posting data into a table?

Posted on 2011-09-09
3
457 Views
Last Modified: 2012-05-12
I have a Stored Procedure that selects data from a remote SQL Server, and inserts this data into a temporary table. I then step through the temp table to update the local server.

When I run the Select * From [RemoteDB].[Table] there is a long (10 second) delay, and my network card utilisation hits 100%.

So I put the Select into a Stored Brocedure on the remote server, and run EXEC [RemoteDB].[SP]. The results return immediately, and network utilisation is low.

BUT when I try:
INSERT INTO [LocalTable]
EXEC [RemoteDB].[SP]

I get the error below:

OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 28
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" was unable to begin a distributed transaction.

I have checked MSDTC is running on the remote server.

Please could someone cast light on overcoming this.

Other relevent info; the servers are stand-alone (no domain). Both are running Win 2k8 (the local server is Standard and the remove R2). SQL is running using the same username on both servers (with the same password). The machines are physically next to each other on the same switch.
0
Comment
Question by:Bird757
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 8

Accepted Solution

by:
Forefront_Data_Solutions earned 500 total points
ID: 36514950
Please review this article from Microsoft:
http://support.microsoft.com/kb/906701

There are several MSDTC items that need to be configured.
0
 

Author Comment

by:Bird757
ID: 36515832
Thanks, but this article is very specific to Great Plains. In the article it specified following the steps below:
 
Make sure that the eConnect object is running under a specific account:
Click Start, click Control Panel, click Administrative Tools, and then click Component Services.
Expand Component Services, expand Computers, expand My Computer, and then expand COM+ Applications.
Right-click eConnect 8 for Great Plains, and then click Properties.

On my server the COM+ Applications area has no "Application" that relates to SQL Server so I am lost as to how I need to proceed.
0
 

Author Closing Comment

by:Bird757
ID: 37284693
The solution was very specific to Great Plains and could not be extended to apply generally. I made a comment to that effect but there was no further communication.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL: I need to add an index on a field 5 50
Estimating my database size 7 51
SQL Server Serialization error 8 47
Utility to Scan for Unauthorized Access 4 36
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A safe way to clean winsxs folder from your windows server 2008 R2 editions
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question