I'm trying to add the AS400 as a linked server in SQL 2008 Express in order to access some tables in a DB on the iSeries. I've tried all of the OLE providers with many different strings and have had no luck establishing a connection. Is this even possible with this version of SQL?
Thanks
I've had trouble with the AS/400 OLE DB Providers as well. So I cheated and used ODBC instead. Create a system DSN to connect to your AS/400. Then create the linked server like this:
EXEC sp_addlinkedserver
@server = 'MyServer',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'AS400'
In the prior example, I gave my system DSN the name of "AS400". Within SQL Express, the server is called "MyServer". Here's the problem with this solution: I used the iSeries Access ODBC provider and, by default, I don't store my password on my local machine. So I had to make sure I was connected to the AS/400 by bringing up a green-screen session first.
Anyway, once the linked server definition is added and you have an active connection, or a password issue worked out with your ODBC connection, you just run a query to get your data like this:
SELECT * FROM MyServer.MySystem.MyLibrary.MyFile
Where "MyServer" is the name you put in the @server= part of the sp_addlinkedserver call. "MySystem" is your AS/400 database name. You can determine this value by logging on to green screen and using the WRKRDBDIRE command. The entry with the remote location of *LOCAL is your database name. "MyLibrary" and "MyFile" are self explanatory.
It feels like a kludgy solution, but it does work.
I've same problem with you, but this solution works for me. After adding the linked server, somehow the library & table doesn't show..but it's connected. Just try this query :
select * from OpenQUery(<name_of_linked_server>, 'select * from <library>.<table>');
You can check the detail here
