May 3, 2011 at 12:15 am
Hi,
I have two SQL Servers Server A and Server B. ServerA has SQL Server 2008 and ServerB has SQL Server 2005.
I need to fetch data from server B to server A and insert it into a table on server A.
For this purpose, I have created a stored proc on server B and for calling that SP I have added server B as linked server on server A.
When I execute this SP from server A using server B, it returns data without any issue.
However when I try to insert this data to a local table on server A, I am getting following error:
OLE DB provider "SQLNCLI10" for linked server "ServerB" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 12
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "ServerB" was unable to begin a distributed transaction.
Note that I have not started any transaction in both the calling and called procs. I am not getting why it is trying to begin a distributed transaction. And how to resolve this issue.
I am using linked server for the first time. Please help.
Thanks,
Garima
May 3, 2011 at 5:22 am
Hi All,
I have been able to resolve the issue after executing the following command on server A:
EXEC sp_serveroption @server = 'ServerB',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;
February 17, 2012 at 1:44 pm
WORKS GREAT!!!!!!
Here is what I did to fix ALL my linked servers
DECLARE @ServerName SYSNAME
, @Message nvarchar(1000)
, @CMD1 nvarchar(max)
--
DECLARE @Server_List Table
( SrvID SMALLINT
, SrvName SYSNAME )
--
Set NoCount ON
--
-- Load up linked server list
--
BEGIN
INSERT INTO @Server_List (SrvID, SrvName)
SELECT SrvID
, SrvName
FROM [master].[SYS].sysservers
ORDER BY SrvID ASC
END
--
SELECT TOP 1 @ServerName = SrvName
FROM @Server_List
ORDER BY SrvID ASC
--
-- Loop through the Linked Server List
--
WHILE EXISTS ( SELECT * FROM @Server_List )
BEGIN
SELECT @Message = 'Server Name is '+ @ServerName
--
RAISERROR (@Message, 10,1) WITH NOWAIT
--
SET @CMD1 = 'EXEC master.dbo.sp_serveroption @server=N'''
+ @ServerName
+ ''', @optname=N''rpc'', @optvalue=N''true'''
Exec sp_executesql @cmd1
--
SET @CMD1 = 'EXEC master.dbo.sp_serveroption @server=N'''
+ @ServerName
+ ''', @optname=N''rpc out'', @optvalue=N''true'''
Exec sp_executesql @cmd1
--
set @cmd1 = 'EXEC master.dbo.sp_serveroption @server = '''
+ @ServerName
+ ''', @optname=N''remote proc transaction promotion'', @optvalue=N''false'''
Exec sp_executesql @stmt=@cmd1,@params=N''
--
DELETE FROM @Server_List WHERE SrvName = @ServerName
--
SELECT TOP 1 @ServerName = SrvName
FROM @Server_List
ORDER BY SrvID ASC
--
END
June 27, 2012 at 9:47 am
Worked perfectly! Thanks so much!
Eric
July 16, 2012 at 7:45 am
I'm going to cry. I had the same problem.
It works perfectly !!!
Thanks so much
July 19, 2012 at 10:49 am
I had this problem between two SQL2005 servers, but your solution does not work for 2005 since
sp_serveroption optname "remote proc transaction promotion" is SQL2008 and up.
Changing the Windows component install does, that is "Enabling network DTC access" does. See the following:
June 27, 2013 at 5:14 am
THANK YOU SOO MUCH ...
I was really struggling with this for 2 days !!!
August 20, 2013 at 8:11 am
Señores, gracias por este gran aporte
April 2, 2015 at 9:07 am
Works Great! Thanks for sharing!
December 3, 2015 at 9:33 pm
Works great. Thanks much
November 29, 2016 at 4:23 am
This was removed by the editor as SPAM
March 5, 2020 at 12:56 am
This solved my problem.
Thank you so much!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply