[Resolved] Open query - error using variables

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)

[Resolved] Open query - error using variables

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-09-22 : 14:19:18

Have following query:

declare @SourceFile varchar(10) 
set @SourceFile = 'F55125'

declare @AliasLibrary varchar(10)
set @AliasLibrary = 'RJADEVLIB' 

DECLARE @SQLSTR VARCHAR(4000) 

SET @SQLSTR = 
'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
''Select                     ztadds,  ztyear,  zt#mt,  ztco,   ztdl01, ztdsc,  ztein,  ztmot,  ztdl02, ztcity
from ''''' + @AliasLibrary + '''''.''''' + @SourceFile + ''''''')'

insert into ZyTax.dbo.JTABLE(report_state, report_year, report_month, company, destination_county,
                             carrier_name, carrier_fein, mode_code, origin_name, origin_city)
EXEC (@SQLSTR)

Gives error:
OLE DB provider "MSDASQL" for linked server "AS400SRV_MSDASQL" returned message "[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token 'RJADEVLIB' was not valid. Valid tokens: ( TABLE LATERAL <IDENTIFIER>.".

The value of my sql string is:
SELECT * FROM OPENQUERY( AS400SRV_MSDASQL,
'Select ztadds, ztyear, zt#mt, ztco, ztdl01, ztdsc, ztein, ztmot, ztdl02, ztcity
from ''RJADEVLIB''.''F55125''')

Now, if I replace this from ''''' + @AliasLibrary + '''''.''''' + @SourceFile + ''''''')' with this from RJADEVLIB.F55125'')' it works.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-22 : 15:25:47

Then I would say that openquery doesn't like the quotes around ''RJADEVLIB''.''F55125''
Should probably be either nothing like you example that works or square brackets instead: [RJADEVLIB].[F55125]

Be One with the Optimizer
TG

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-22 : 15:31:37

It probably doesn't like zt#mt as column name either

Jim

Everyday I learn something that somebody else already knew

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-22 : 15:31:51

You may want to check your ODBC setup Look for the compatability setting for AS400(iService) or SQL. When I was doing a lot of AS400 work I would get quirky things like that.

John

"The smoke monster is just the Others doing barbecue"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-09-22 : 16:26:18

This works:

declare @SourceFile varchar(10) 
set @SourceFile = 'F55125'

declare @AliasLibrary varchar(10)
set @AliasLibrary = 'RJADEVLIB' 

declare @String varchar(25)
set @String = @AliasLibrary + '.' + @SourceFile 

DECLARE @SQLSTR VARCHAR(4000) 

SET @SQLSTR = 
'SELECT * FROM OPENQUERY(AS400SRV_MSDASQL, 
''Select *
From  ' + @String + ' '')' 

EXEC (@SQLSTR)

So I guess I must use single quotes only. Thank you guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources