Solved

SQL Server OpenQuery to DB2 file member

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now