1

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.

0

2 Answers 2

3

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;
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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.