Exec SP on Linked server and put that in temp table

Question

Need some help on the below issue:

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

Result: successful execution

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

Result

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.

Case 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.

---

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.

Answer 1

You have (I believe) two options here:

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

   /assume (here and below) that [database2_server2] is the name of the linked server/

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

OR

2. 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'

   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