Solved

SQL Server OpenQuery to DB2 file member

Posted on 2009-07-08
4
1,229 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
  • 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

810 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