January 10, 2005 at 2:43 pm
I have been working all day on a linked server connection to an AS400. I created an ODBC for the AS400 on the server. Then I created a linked server utilizing the ODBC. I can open the linked server and get the list of tables. When I run query analyzer with an select statement I get this error.
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver (32-bit)]Driver not capable.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned 0x80004005: ].
Any suggestions?
Jan
January 11, 2005 at 6:10 am
I have had the AS400 linked server approach up and running to see what it could do. We used the OLEDB provider instead of ODBC and it worked, except that performance is painful.
You must use the Client Access 5.1 OLEDB drivers. You can install these right from the CA 5.1 install CD. When you configure the linked server make sure you have 'IBMDA400' in the provider text box, and the DNS name of your 400 in the 'Data Source' box.
From here it gets dicey. We could only get access to the library containing the data we wanted by creating a user profile on the 400 with the same name as the library. FOr instance, if you want to link to data in the library 'AS400DB' then you must create a user profile with that name. On the security tab of the linked server properties, specify this user name and password. You should then see a list of tables in this library when you select 'tables' under the linked server.
Another note is that when you wish to query this linked server you need to use the Openquery syntax like this:
SELECT * FROM OPENQUERY
(YOUR_LINKED_SERVER_NAME, select * from mytable where mycolumn = 1)
Hope this helps.
January 11, 2005 at 9:55 am
We have had success using the linked server connected to an ODBC driver as suggested by the original poster.
We use the openquery syntax as suggested above and access multiple libraries by using mylib.mytable in place of mytable as suggested above.
Please post your resolution.
February 3, 2005 at 2:11 pm
Hi,
I have been struggling to get the linked server set up from SQL SERVER to Corporate AS 400 Server. Can you please walk me through the process step by step. This is my first time experience using SQL SERVER. if you could send me a print screen of the properties that i need to set up in the linked server, i really appreciate that.
Waiting for help!!
thanks a lot in advance,
vakula
PS: you can mail me at vakula.manikonda@valueoptions.com
February 3, 2005 at 3:00 pm
Vakula, I am using an ODBC for my linked AS400 server. Make sure you have the following covered:
1) iSeries Access is installed on your SQL Server (these are the necessary ODBC and OLEDB drivers for connecting to OS400/DB2)
2) make sure iSeries Access is up to date with at least a recent SP cooresponding to your OS400 version
3) If you are using the ODBC driver, make sure you have a DSN setup under system DSN's.
4) Verify that your Linked Server connection is using a proper OS400 user ID.
5) Make sure you ODBC setup matches that of a known good configuration (see below).
ON A WORKSTATION, I suggest you test some of the above items. Using something like MS Access, connect to your AS400 using the same version of iSeries Access you have on the SQL server, and using the same ID your are trying to use on the SQL server, and with the same ODBC setup you are trying to use on the SQL server.
If you can connect properly with all of those items/setups on a workstation, SQL should be able to link to your AS400 using the same items.
Good Luck.
Ryan Hunt
February 3, 2005 at 3:15 pm
Hi Ryan,
Thanks for the help!! I have some questions about the procedure. I have IBM client access on my machine. and i am sure my System administrator had installed the same on the server.( i will verify this). But when i click on new linked servers, under provider list, i dont see "IBM client access ODBC drvier 32 bit", as the way I see when I try to set up the system DSN on my local machine. When i tried to connect to the library i want on AS 400 using the System DSN on my local machine thorugh microsoft access, it works perfectly fine the my username and password and it showed the tables to link.
I created a system DSN on the server for AS 400. and i have selected microsoft OLE DB AS 400 provider. then i dont know what to put in product name data source name, provider string, location, catalog. also other options that i need ot set in provider options, security tab in the new linked server properties set-up window.
Please help!!
thanks
vakula
February 3, 2005 at 3:19 pm
Use the Microsoft OLE DB Provider for ODBC Driver and the DSN is the same as the data source from the ODBC driver. The other fields are blank.
On the security page, use "be made using this security context with your username and pw
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply