Bird757
asked on
Cross-Server MS SQL RPC call works but fails when posting data into a table?
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.