How to save result from a Stored Procedure in a Linked Server to a variable or table?

Question

I need to read the results of a stored procedure in a linked server, and store them in a variable, or a temporary table if not possible.

I can do this, and I get the correct result:

EXEC ('EXEC DBName.dbo.procedure_name') AT [xxx.xx.xxx.xx]

However, when I try to do something like this:

DECLARE @Result NVARCHAR(200) = (EXEC ('EXEC DBName.dbo.procedure_name') AT [xxx.xx.xxx.xx])

Or something like this:

INSERT #TempTable EXEC ('EXEC DBName.dbo.procedure_name') AT [xxx.xx.xxx.xx]

I get this error

The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server was unable to begin a distributed transaction

How can I store this result somewhere? It's needed for a larger process, so it has to be stored.

Answer 1

If it is a single value as shown in the question, and you can modify the remote procedure, another option is to use an output parameter:

-- On the remote server
CREATE PROCEDURE dbo.ProcName
 @Result nvarchar(200) OUTPUT
AS
 ...
 SET @Result = ...

Then on the local instance:

DECLARE @Result nvarchar(200);
EXECUTE [xxx.xx.xxx.xx].DBName.dbo.ProcName @Result OUTPUT;
SELECT @Result;

Answer 2

Your INSERT creates a local transaction, in which SQL Server is trying to enlist the remote procedure invocation using a distributed transaction, which is failing.

So disable distributed transaction promotion for the linked server, eg

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

then it should work, eg

exec [MyLinkedServer].SomeDb.sys.sp_executesql N'create proc foo as select ''hello'' msg'
go
drop table if exists #TempTable
create table #TempTable( m varchar(200))
INSERT #TempTable EXEC ('EXEC SomeDb.dbo.foo') AT [MyLinkedServer]
select * from #TempTable

Or you can use an output parameter to capture a single scalar value from the remote proc,

declare @msg varchar(200);
exec [MyLinkedServer].SomeDb.sys.sp_executesql N'
declare @t table(msg varchar(200))
insert into @t(msg) 
exec dbo.foo
set @msg = (select msg from @t)', N'@msg varchar(200) output', @msg = @msg output;
select @msg msg