AS400 linked server in SQL Server problem

Dear experts,
I succeed to create a linked server to AS400 with you help:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21405580.html

Now i still have a few problems:
1- I see different tables than when linking with MS Access!

2- When creating a view like that:
select * from hc3_linked.s44g5402.shhom03.ahzmp ahzmp_1
i got all the column titles, but no records and an error message:
"[microsoft][odbc sql server driver][sql server] ole db provider 'msdasql' reported an error. The provider did not give any information about the error."

3- in the properties > General tab of the linked server, I only entered the DSN name (HC3) as Data source.
Should i fill Product Name or the 3 other fields?

FYI, i can link through MS Access like this:
"ODBC;DSN=HC3;;TABLE=shhom03.ahzmp"

Thanks in advance.
prismabiAsked:
Who is Participating?
 
Melih SARICAOwnerCommented:
try this to create a view
 select * from openquery(hc3_linked,'select * from s44g5402.shhom03.ahzmp ahzmp_1 ')


Meli SARICA

0
 
prismabiAuthor Commented:
Dear Meli,
Thank you for your quick answer and sorry for the delay.

 select * from openquery(hc3_linked,'select * from s44g5402.shhom03.ahzmp ahzmp_1 ')
give the same error as mentionned.

 select TOP 1 * from openquery(hc3_linked,'select * from s44g5402.shhom03.ahzmp ahzmp_1 ')
is working but returning only 1 record of course, but TOP 2 is not working!
I tried a lot of configurations, but it is always the same "no information" error.

Do you have any further idea?
Thanks
0
 
Melih SARICAOwnerCommented:
ahzmp ahzmp_1 is this a table name ?

or a table with a member ?

if its a member then try ahzmp.ahzmp_1 ( . (dot) between table and membername)


Melih SARICA
0
 
prismabiAuthor Commented:
Dear Meli,
Thank you for your help and sorry for the delay.
ahzmp  is a table name, and ahzmp_1 is the alias created by SQL designer.
I'll have to experiment more to get it work.
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.