Get SQL Server remote procedure result

Question

I have 2 SQL Server 2005, I want to pull data from A to B.

I execute this code on B.:

create table #res (
   ValueID int,
   [Timestamp] varchar(32),
   RealValue float,
   Quality int,
   Flags int
);

insert into #res(ValueId, [Timestamp], [RealValue], [Quality], [Flags])
   exec ('exec [CC_ExternalBrowsing].[dbo].[cc_sp_readtags] @List=''1;2;3;4;5'', @TimeBegin=''0000-00-00 00:05:00.000'', @TimeEnd=''0000-00-00 00:00:00.000''') AT [WINCCTESZT]

select * from #res;
drop table #res

Exec part runs fine (without the previous insert line). I can see data in SSMS, but I can't insert data into the temp table

I get this error:

The procedure 'sys.addlinkedserver' cannot be executed within a transaction.

Any ideas?

Thanks

Zui

score 0 · Answer 1 · answered Jun 01 '14 at 18:20

Use a table-valued variable instead of the explicit Temp table - as table-creation cannot occur within a transaction. Try this instead:

declare @res table (
   ValueID     int,
   [Timestamp] varchar(32),
   RealValue   float,
   Quality     int,
   Flags       int
);

insert into @res(ValueId, [Timestamp], [RealValue], [Quality], [Flags])
   exec ('exec [CC_ExternalBrowsing].[dbo].[cc_sp_readtags] @List=''1;2;3;4;5'', @TimeBegin=''0000-00-00 00:05:00.000'', @TimeEnd=''0000-00-00 00:00:00.000''') AT [WINCCTESZT]

select * from @res;

-- drop table @res    -- no longer needed

Get SQL Server remote procedure result

1 Answers1