quote:

---

Originally posted by getut

Please help me. I've tried this every way I can find on the internet and in examples and can't get the result of an exec at to store into a variable. The "at" is an IBM iSeries if that matters. The query executes and I get my single field result as expected, but no variant I have tried will set the variable. Of course I started with simply set @factord = blah blah but that blew up in my face also.

Here is my latest "close but no cookie".

declare @factord varchar(75)
declare @command nvarchar(4000)
declare @sernum int
set @sernum = 8875620
set @command = 'exec (''select prsesp from upspf.pr where prser=?'','+cast(@sernum as varchar)+') at iseries'
exec sp_executeSQl @command, N'@factordout varchar(75) output', @factordout=@factord output;
select @factord

---

declare @factord varchar(75)
declare @command nvarchar(4000)
declare @sernum int
set @sernum = 8875620
set @command = 'exec (''select @factordout=prsesp from upspf.pr where prser=?'','+cast(@sernum as varchar)+') at iseries'
exec sp_executeSQl @command, N'@factordout varchar(75) output', @factordout=@factord output;
select @factord

quote:

---

You need to assign the value the output variable in the query.

declare @factord varchar(75)
declare @command nvarchar(4000)
declare @sernum int
set @sernum = 8875620
set @command = 'exec (''select @factordout=prsesp from upspf.pr where prser=?'','+cast(@sernum as varchar)+') at iseries'
exec sp_executeSQl @command, N'@factordout varchar(75) output', @factordout=@factord output;
select @factord

---

quote:

---

Originally posted by James K

I am not familiar with iSeries, so not able to offer much help there. The syntax I posted applies when the remote machine is also a SQL Server.

Is there a need to use dynamic sql and sp_executesql? Can you query directly against the linked server?

---

INSERT mySQLServerTable EXEC (@ReallyElaborateDB2SQLStatementUsingOpenQuery)
DECLARE @factord varchar(75)
SELECT @factord=prsesp FROM mySQLServerTable