Solved

SQL Server OpenQuery to DB2 file member

Posted on 2009-07-08
4
1,241 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:ntccps
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 4

Expert Comment

by:Igor-K
ID: 24804435
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
 

Author Comment

by:ntccps
ID: 24805043
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
 
LVL 4

Expert Comment

by:Igor-K
ID: 24811818
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
 

Accepted Solution

by:
ntccps earned 0 total points
ID: 24813664
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
query output (script) from a stored procedure 4 35
Freeze portion of datamart 2 21
Can I skip a node in XML? 9 30
help converting varchar to date 14 25
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question