OpenQuery to db2/400

[snufse1]

Have following code:

declare @JobNumber1 char(12)
set @JobNumber1 = ' 3505040'
SELECT *
FROM OPENQUERY(AS400SRV_MSDASQL, 'Call QGPL.get_eqmqty(?)' @JobNumber1)

Get error:

Incorrect syntax near '@JobNumber1'.

 

[RiverGuy]

You need to concatenate the value
FROM OPENQUERY(AS400SRV_MSDASQL, 'Call QGPL.get_eqmqty(?)' + @JobNumber1)

 

[snufse1]

Getting error:

FROM OPENQUERY(AS400SRV_MSDASQL, 'Call QGPL.get_eqmqty(?)' + @JobNumber1)

Incorrect syntax near '+'.

 

[sqlfable]

You will need to use dynamic sql for this. The batch you send to DB2 in openquery will need to be enclosed in quotes also (as the final result)

Before going much farther however I would test to make sure the statement works with a static value

 

[snufse1]

I've tried this:

declare @JobNumber1 char(12)
set @JobNumber1 = ' 3505040'
DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = 'AS400SRV_MSDASQL'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = ' Call QGPL.get_eqmqty(?),' + @JobNumber1 + ')'
EXEC (@OPENQUERY+@TSQL)

Value of string:

SELECT * FROM OPENQUERY(AS400SRV_MSDASQL,' Call QGPL.get_eqmqty(?), 3505040)

Getting error:

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ' Call QGPL.get_eqmqty(?), 3505040)'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' Call QGPL.get_eqmqty(?), 3505040)'.

 

[RiverGuy]

Looks like you need a single quote after the 3505040.

 

[snufse1]

Now, tried this:

declare @JobNumber1 char(12)
set @JobNumber1 = ' 3505040'
DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = 'AS400SRV_MSDASQL'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = ' Call QGPL.get_eqmqty(?),' + @JobNumber1 + '''' + ')'
EXEC (@OPENQUERY+@TSQL)


String value:

SELECT * FROM OPENQUERY(AS400SRV_MSDASQL,' Call QGPL.get_eqmqty(?), 3505040')



Error:

OLE DB provider "MSDASQL" for linked server "AS400SRV_MSDASQL" returned message "[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token , was not valid. Valid tokens: <END-OF-STATEMENT>.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query " Call QGPL.get_eqmqty(?), 3505040" for execution against OLE DB provider "MSDASQL" for linked server "AS400SRV_MSDASQL".

 

[RiverGuy]

It's been a couple years since I've worked much with AS400, but if memory recalls, your string should reflect the command syntax on the Client Access terminal. Does the question mark denote a parameter place holder? What happens if you try the following:

'Call QGPL.get_eqmqty 3505040'

 

[snufse1]

Yes, the '?' indicates parameter place holder.

Here is a query that is working:

declare @JobNumber1 char(12)
set @JobNumber1 = ' 3505040'
Exec ('Call QGPL.get_eqmqty(?)', @JobNumber1) AT AS400SRV_MSDASQL

This query is not using 'OpenQuery', it calls a sp on the AS400 directly and returns result set. However, I would like to use 'OpenQuery' or 'OpenRowSet'

 

[RiverGuy]

I meant to try replacing this part

' Call QGPL.get_eqmqty(?), 3505040'

with this 'Call QGPL.get_eqmqty 3505040'

still using Open Query.

 

[snufse1]

Here we go:

declare @JobNumber1 char(12)
set @JobNumber1 = ' 3505040'
DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = 'AS400SRV_MSDASQL'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
--SET @TSQL = ' Call QGPL.get_eqmqty(?),' + @JobNumber1 + '''' + ')'
SET @TSQL = ' Call QGPL.get_eqmqty 3505040' + '''' + ')'
EXEC (@OPENQUERY+@TSQL)


String value:
SELECT * FROM OPENQUERY(AS400SRV_MSDASQL,' Call QGPL.get_eqmqty 3505040')


Error:
OLE DB provider "MSDASQL" for linked server "AS400SRV_MSDASQL" returned message "[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token 3505040 was not valid. Valid tokens: ( INTO USING.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query " Call QGPL.get_eqmqty 3505040" for execution against OLE DB provider "MSDASQL" for linked server "AS400SRV_MSDASQL".