链接服务器上的Exec SP并将其放在临时表中 [英] Exec SP on Linked server and put that in temp table

问题描述

在以下问题上需要一些帮助:

Need some help on the below issue:

案例1 :存储过程在服务器1上-调用来自server1

Case 1 : stored procedure is on server 1 - call is from server1

declare @tempCountry table (countryname char(50))
insert into @tempCountry
    exec [database1_server1].[dbo].[getcountrylist]
Select * from @tempCountry

结果:成功执行

案例2 :i如果正在使用像这样的链接服务器从另一台服务器调用同一存储过程:

Case2 : iIf this same stored procedure is being called from a different server using linked server like this :

declare @tempCountry table (countryname char(50))
insert into @tempCountry
    exec [database2_server2].[database1_server1].[dbo].[getcountrylist]
Select * from @tempCountry

结果

MSG 7391，第16级，状态2，第2行
由于链接服务器"Server2_Database2"的OLEDB提供程序"SQLNCLI"无法开始分布式事务，因此无法执行该操作.

Msg 7391, level 16, state 2, line 2
The operation could not be performed because OLEDB provider "SQLNCLI" for linkedserver "Server2_Database2" was unable to begin a distributed transaction.

案例3

但是当尝试单独执行存储过程时(没有插入临时表)，如下所示

But when tried to execute the stored procedure separately [without temp table insertion] like below

exec [database2_server2].[database1_server1].[dbo].[getcountrylist]

结果:正在执行存储过程而没有任何错误并返回数据.

Result: that is executing the stored procedure without any error and returning data.

我忘了提到正在使用SQL Server2005.根据服务器管理员的说法，您建议我使用的功能在2005年不可用.

I forgot to mention that am using SQL Server 2005. As per the server administrator, the feature you've suggested that I use is not available in 2005.

推荐答案

您在这里有两个选择:

1. 为避免使用MSDTC(以及所有这些与分布式事务相关的令人讨厌的事情) ="http://msdn.microsoft.com/en-us/library/ms188427.aspx" rel ="noreferrer"> OPENQUERY 行集函数

1. To try to avoid the usage of MSDTC (and all these not pleasant things related to Distributed Transactions) by using OPENQUERY rowset function

/(在此及以下)假设[database2_server2]是链接服务器的名称/

declare @tempCountry table (countryname char(50)) insert into @tempCountry select * from openquery([database2_server2], '[database1_server1].[dbo].[getcountrylist]') select * from @tempCountry

declare @tempCountry table (countryname char(50)) insert into @tempCountry select * from openquery([database2_server2], '[database1_server1].[dbo].[getcountrylist]') select * from @tempCountry

OR

1. 您可以将链接服务器的选项Enable Promotion Of Distributed Transaction设置为False，以防止本地事务促进分布式事务并因此促进MSDTC的使用:

1. You can set the linked server's option Enable Promotion Of Distributed Transaction to False in order to prevent the local transaction to promote the distributed transaction and therefore use of MSDTC:

EXEC master.dbo.sp_serveroption @server = N'database2_server2', @optname = N'remote proc transaction promotion', @optvalue = N'false'

EXEC master.dbo.sp_serveroption @server = N'database2_server2', @optname = N'remote proc transaction promotion', @optvalue = N'false'

，您的原始查询应该可以正常运行:

and your original query should work fine:

declare @tempCountry table (countryname char(50)) insert into @tempCountry exec [database2_server2].[database1_server1].[dbo].[getcountrylist] select * from @tempCountry

declare @tempCountry table (countryname char(50)) insert into @tempCountry exec [database2_server2].[database1_server1].[dbo].[getcountrylist] select * from @tempCountry

这篇关于链接服务器上的Exec SP并将其放在临时表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！