This one's a tough-e

[tman24m]

I've got a linked server from MS SQL 2K to AS400. I used to be able to write a T-SQL statement "Select * From AS400.CATALOG.LIBRARY.FILE". Somewhere I decided that I wasn't going to use linked server and went directly to importing the data into SQL. I've since set up a new DSN connection but now for some reason I have to do "Select * From OpenQuery(AS400, 'strSQL') to get the same results. Not sure what I've changed there but ok.

Does anyone know how to use a variable in the 'strSQL' argument of the OpenQuery method? Here is what I've got:

DECLARE @model varchar(12)
Select @model = '3300HCC'
Select * From OpenQuery(AS400,
'Select WZDEPT, WZWCID, WZDESC, RZPAPN , MRFMOD, MRNOPR, OMXOPR, RZFROM, RZTILL
From MSTLIB.MMPWKC WC
INNER JOIN MSTLIB.MMPMR MR ON WC.WZWCID = MR.MRFWK1
INNER JOIN MSTLIB.MMPOM OM ON MR.MRNOPR = OM.OMNOPR
INNER JOIN MSTLIB.MMPRTG RTG ON OM.OMNOPR = RTG.RZROPR
WHERE RZPAPN = ''41850001 '' AND MRFMOD = ' @model '')
WHERE
RZFROM <= master.dbo.DateConv (GETDATE(), 'yyyymmdd')
AND RZTILL >= master.dbo.DateConv (GETDATE(), 'yyyymmdd')

Returns
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near '@model'.

However, this works fine

Select * From OpenQuery(AS400,
'Select WZDEPT, WZWCID, WZDESC, RZPAPN, MRFMOD, MRNOPR, OMXOPR, RZFROM, RZTILL
From MSTLIB.MMPWKC WC
INNER JOIN MSTLIB.MMPMR MR ON WC.WZWCID = MR.MRFWK1
INNER JOIN MSTLIB.MMPOM OM ON MR.MRNOPR = OM.OMNOPR
INNER JOIN MSTLIB.MMPRTG RTG ON OM.OMNOPR = RTG.RZROPR
WHERE RZPAPN = ''41850001 ''
AND MRFMOD = ''3300HCC''')
WHERE
RZFROM <= master.dbo.DateConv (GETDATE(), 'yyyymmdd')
AND RZTILL >= master.dbo.DateConv (GETDATE(), 'yyyymmdd')


If anyone can help, please do.

PS - Will someone please write a book on this AS400/SQL interaction?

Thanks
T Wayman