SQL Server OpenQuery to DB2 file member

I'm trying to use SQL Server OpenQuery to read data directly from a DB2 file member (code below). I don't want to do anything special on the IBM side to make this possible, like creating aliases. I just want to be able to change the file member in my OpenQuery statement and get the data I need.

Code below results in this error:
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token ( was not valid.

I've tried surrounding different parts with single quotes, double quotes, slashes, etc. Thanks in advance.
SELECT * FROM OPENQUERY(linkedserver, 'SELECT * FROM Library.File(member)')

Open in new window

ntccpsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Igor-KCommented:
If I understand correctly, you need to dynamically specify the query you are executing.  The openquery construction does not allow parameters.  And maybe the member should be separated by dot rather brackets.  YOu would need to use something like this:
--assuming you receive one column name id of type int
declare @n nvarchar(2000),@member nvarchar(200)
set @member='member'
set @n='SELECT id FROM OPENQUERY(linkedserver, ''SELECT * FROM Library.File.'+@member+''')'
create table #t(id int)
insert into #t(id)
exec(@n)
 
0
ntccpsAuthor Commented:
In the simplest case (b/c I don't need to change the member as frequently as you're perceiving), using your select statement causes confusion in the 4-part name. Library.File.member is interpreted as owner.database.table, which isn't the case.
So
SELECT id FROM OPENQUERY(linkedserver, 'SELECT * FROM Library.File.member')
doesn't work, either.
Including the 4-part name results in an error, as well:
SELECT * FROM OPENQUERY(linkedserver, 'SELECT * FROM Server.Library.File.member')
returns
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token . was not valid.

It appears anything after the file doesn't get parsed or interpreted correctly.
0
Igor-KCommented:
What abount this:

SELECT id FROM OPENQUERY(linkedserver, 'SELECT * FROM File')

The library should be the default library for the user used by the link server.
0
ntccpsAuthor Commented:
Right, but it's not the file I'm concerned about, I can get to that consistently, it's which file member gets returned. That's what isn't consistent. Like this:
File = Sales
Mem1 = 2008
Mem2 = 2009
Mem3 = 2010
and so on.

I want to be able to query the Sales(2009) file member from SQL Server, specifically.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.