asked on

AS400 linked server in SQL Server problem

Dear experts,
I succeed to create a linked server to AS400 with you help:
https://www.experts-exchange.com/questions/21405580/How-to-create-in-SQL-SERVER-a-linked-server-to-AS400.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.

ASKER CERTIFIED SOLUTION

Melih SARICA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

prismabi

ASKER

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

Melih SARICA

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

prismabi

ASKER

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.