Solved

SQL Server OpenQuery to DB2 file member

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

617 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