Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 "exec at" into a variable

Author  Topic 

getut
Starting Member

7 Posts

Posted - 2013-04-23 : 10:19:33
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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-23 : 10:44:39
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

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
Go to Top of Page

getut
Starting Member

7 Posts

Posted - 2013-04-23 : 11:40:26
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




I'm sorry but I'm going to need more hand holding than that. I don't think iSeries understands @ variables. I've been playing with it and reading more, it looks like iseries uses : variables but I can't get it defined in a way that works.

I've simplified to this just for the testing phase. Its not working. All versions of variable declaration that I have found so far are failing. create or replace variable, declare @variable, declare :variable, declare variable all give errors.

declare @factord varchar(5)
exec ('select prsesp into :factordout from upspf.pr where prser=?',@factord output,8875620) at iseries

Current error is:
Server: Msg 7215, Level 17, State 1, Line 2
Could not execute statement on remote server 'iseries'.
OLE DB provider "IBMDA400" for linked server "iseries" returned message "SQL0312: Variable FACTORDOUT not defined or not usable.
Cause . . . . . : The variable FACTORDOUT appears in the SQL statement, but one of the following conditions exists: -- No declaration for the variable exists. -- The attributes are not correct for the use specified. -- The host variable was specified in dynamic SQL. Host variables are not valid in dynamic SQL. -- In REXX, host variable names cannot contain embedded blanks. -- The variable name is used in the routine body of an SQL procedure or function, but the variable is not declared as an SQL variable or parameter. The scope of an SQL variable is the compound statement that contains the declaration. -- The variable is used in the routine body of an SQL trigger, but the variable is not declared as an SQL variable or the variable is an OLD transition variable and cannot be modified. -- The variable is a transition variable in an AFTER trigger and is used in statement where the variable could be modified. Modifying transition variables in AFTER triggers is not allowed. Recovery . . . : Do one of the following and try the request again. -- Verify that FACTORDOUT is spelled correctly in the SQL statement. -- Verify that the program contains a declaration for that variable. -- Verify that the attributes of the variable are compatible with its use in the statement. -- Use parameter markers in dynamic SQL instead of host variables. -- Remove embedded blanks from REXX host variable names. -- Declare the variable as an SQL variable or parameter in the SQL procedure or function. -- Declare the variable as an SQL variable or specify a NEW transition variable when the variable is modified in an SQL trigger. -- Remove the transition variable from the statement. Copying the transition variable to a local variable and then using the local variable in the statement is also acceptable."
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-23 : 12:13:29
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?
Go to Top of Page

getut
Starting Member

7 Posts

Posted - 2013-04-23 : 15:05:53
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?



Yes, it needs to be dynamic because the built query changes through many iterations in a much larger program.

I think I see part of the problem. In all cases I can find in examples it seems to assume a stored procedure is being defined. I don't have the ability to do that.

I have to take a single field "normal" result set from an execute statement or sq_executesql statement and get the value into a variable. I there a way I can accomplish that?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-23 : 16:18:38
There is an issue here even if the linked server was a SQL Server. You are executing at the linked server, but exec-at has no way of returning a parameter. For that you have to use sp_executesql. So the current approach would not work. You would have to use a linked server query / openquery. http://msdn.microsoft.com/en-us/library/ms188427.aspx
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-04-23 : 16:54:40
I can say with some very annoyed experience that the only reliable way to get anything from iSeries/AS400/DB2 linked servers with the available ODBC and OLEDB drivers is to dump the results into a SQL Server table. I've never been able to get DB2 procedures to execute via 4-part names or put anything into variables.

Once it's in a SQL Server table you can do this (assuming a single row in the table):
INSERT mySQLServerTable EXEC (@ReallyElaborateDB2SQLStatementUsingOpenQuery)
DECLARE @factord varchar(75)
SELECT @factord=prsesp FROM mySQLServerTable
When this wasn't satisfactory, I did a lot of hacking on the iSeries import/export utility (can't remember the name now) to export to text files which I then imported into SQL Server. It's possible and lot harder than doing the above, so I don't recommend it unless absolutely necessary.
Go to Top of Page
   

- Advertisement -