AS/400 DB2 linked server problem

I'm using MS-SQL management studio and added linked server on my environment.
I can use openquery for my linked server.

ex)
select * from openquery(linked_server, 'SELECT * FROM [LIB_NAME].[PF_NAME]')

It works fine.
But, I wanna know if I can change the above expression like this.

select * from [linked_server].[LIB_NAME].[PF_NAME]

Of course, I failed.. Please let me know how this can work.
Thanks.




LVL 1
co2twAsked:
Who is Participating?
 
momi_sabagCommented:
try

EG Query: SELECT * FROM AS400.HJL.BEL.REL
In this query:
AS400 = Linked Server Name on SQL
HJL = AS400 Hostname / Machine Name (this could also be the server IP)
BEL = The library from which we want to select data
REL = Table

took it from
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138160
0
 
momi_sabagCommented:
sql server uses 4 part names, so you just need to try some combinations for your query
try

select * from [linked_server]..[LIB_NAME].[PF_NAME]
select * from [linked_server].[LIB_NAME]..[PF_NAME]
0
 
co2twAuthor Commented:
I tried your query but I also failed.
please give me the descript of the 4 parts.

[linked_server].[what?].[LIB_NAME].[PF_NAME]
[linked_server].[LIB_NAME].[what?].[PF_NAME]
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
co2twAuthor Commented:

I also tried but failed.

I'm sure that my server's machine name is correct and I also tried my ip address of the server.
But failed...
Do you have any other clue for this?
0
 
momi_sabagCommented:
what is the exact error message you get?
0
 
co2twAuthor Commented:
the error message is below.

An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "DNA".
0
 
momi_sabagCommented:
is it possible that this is a permission issue?
can you trace the As400 DB2 and check if your request ever made it through
0
 
co2twAuthor Commented:
I didn't solve this problem yet but I'll accept the answer.
If the description for the connection 4 parts is correct, there might be a clue.
Anyway thanks.
0
 
co2twAuthor Commented:
It didn't solve my problem absolutely but It gives me a clue to solve.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.