AS 400 linked server openquery and member specification

[Daniele71SV]

I've succefully set up a linked server to an as 400. I can do such thing as retrieving all record from tables through the OPENQUERY statement. However, when I query some table (that I need, NOW) the result set contains zero record. I known that it's a problem of specifying the MEMBER of that table. Anyone could help?

Note: I'm sure I have permission to view the record in the table, so the problem is not about user rights.

Thank you very much.

Daniele

 

[tlbroadbent]

Could you post your query? I can't possibly guess at what you are doing! Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[Daniele71SV]

This query is OK, I mean it returns all rows from the table:

SELECT * FROM OPENQUERY(AS400, 'SELECT * FROM EPFILC1.MFFFARF0 WHERE FRNDO$ = 51504')

This one returns no rows, even if I known there are records in the table:

SELECT * FROM OPENQUERY(AS400, 'SELECT * FROM EPFILC1.AFERDEF0')

I think that in order to get rows from table AFERDEF0 I have to specify the correct member of AFERDEF0, as it's assumed a default member which contains no rows at all. Anyone could help?

Thanks.

 

[Zathras]

Assuming that there isn't a typo and you really are referencing two different tables (MFFFARF0 and AFERDEF0), then try one of these:

SELECT * FROM EPFILC1.AFERDEF0.MYMEMBER

or

SELECT * FROM EPFILC1\AFERDEF0(MYMEMBER)

Hope that helps.

 

[tlbroadbent]

'SELECT * FROM EPFILC1.AFERDEF0' should return all rows. Is the table EPFILC1.AFERDEF0 in the same database as EPFILC1.MFFFARF0? Have you tried to use the 3-part name in the select statement?

'SELECT * FROM DBNAME.EPFILC1.AFERDEF0' Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[Daniele71SV]

OK, it doesn't work anyway!

1. AFERDEF0 and MFFFARF0 are in the same database; if I do this:

EXEC sp_tables_ex 'AS400'

I get all the tables and views in AS400 linked server, and there are both AFERDEF0 and MFFFARF0.

2. I've tried

SELECT * FROM EPFILC1.AFERDEF0.ANNO02

and

SELECT * FROM EPFILC1\AFERDEF0(ANNO02)

None works! :-(

Any ideas?