August 10, 2006 at 11:04 am
I'm a total newbie to this so please be patient. I am able to connect to our AS400 and link tables with Access via our HiT ODBC Driver. I recently installed SQL Server 2005 Enterprise and also installed the Microsoft OLE DB Provider for DB2.
The first thing I notice is when I set up the Microsoft OLE DB Provider for DB2 connection, I can connect, but when the sample query runs in the validation window, I don't see any of the libraries / tables. Is this a problem?
Secondly, I did a "Linked Server" in SQL 2005 and am able to do simple queries using the 4 part naming convention but sometimes I get the message that the field can't be bound. What's up with that?
How would I (if possible) attach / register the AS400 so I can see the AS400 tables? My ultimate goal is to be able to replicate via a snapshot so I can run reports from SQL 2005.
HELP!
August 11, 2006 at 6:35 am
You might try the iSeries client access driver provided by IBM. We have always had better results using that one. Although, we don't use Access and have never tried to "link" tables or register the database directly from Enterprise Manager or Managment Studio.
Dan
August 11, 2006 at 2:34 pm
Bear with me since these applied to SQL 2000, but don't know if is true for 2005.
You don't see the libraries because in the default setup of the linked server there is no where to enter the library list needed. You will be able to access only the libraries/files that the account used by the linked server is allowed to see on the AS/400 side. Again, this may have changed on 2005 and may be a place on the link server configuration to set it up.
About the fields not being bound, you will have to try several combinations. Depending on the type of provider you used for the linked server (Client Access ODBC, IBMDA400, etc.) the syntaxis to remote access files changes. I. e. I have an app that uses an ODBC and have to use the notation Library.File. But another uses OLE DB and the syntax there is Library/File.
One way to see the proper libraries will be to use Client Access' ODBC provider, and on the setup tabs there is a line where you can enter the library list. This works fine, but ODBC is slower.
Imho, the best (and fastest) way to extract data from the AS/400 is to setup a DTS job to pull the data. Linked tables works, but it may be too slow. So test, test and benchmark everything.
Hope this helps.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply