Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dtrivedi
Posting Yak Master
153 Posts |
 Posted - 2010-08-09 : 15:57:28
|
| has anyone every used AS400? I wanted to know if you can export a database from as400 to excel or sql server? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-08-09 : 16:10:31
|
| I've connected to an AS/400 using Microsoft's OLEDB driver. The iSeries driver provided by IBM also worked but not with SSIS (not for me anyway). I mostly used DTS and linked servers to pull data into SQL Server. Never tried it with Excel.One thing to look out for with the AS/400: it can map text files as tables, and they can perform VERY BADLY if you do JOINs on them because they're not indexed. There's a SYSTABLES view that has a column that tells you if it is a logical or physical table, or a view. I can't remember the name but it's easy to find.If you go the linked server route, as usual you should avoid JOINs between tables on different servers. Pull down all the data you think you'll need to your SQL Server and work with the local copy instead. Or if you use SSIS do all the joins there. |
 |
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-08-09 : 16:12:34
|
| there aren't alot of joins as far as i know can you give me some instructions on where to begin? I can try the iseries |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-08-09 : 16:35:49
|
| I had a lot of trouble getting the connection string right. DTS got it right automatically but I couldn't use the settings it exported in a linked server or SSIS. Took a lot of trial and error before I got it working (SSIS never worked for me at all).I don't work at the place where I used this and I don't remember the details, sorry. Best suggestion I can make is to use SSIS or DTS to connect to the AS/400 and let it build the connection with the wizard. Once you get connected you can start grabbing data into SQL Server and doing more work there. Once you're comfortable you can try setting up a linked server and see if that works for you.By the way, if you have the iSeries install package it should have some bcp type utilities to pull data from the AS/400 into text files. Look in the iSeries help file for "RTOPCB" for details. There's also a Data Import/Export Wizard that uses this utility and can configure a transfer profile. It's pretty fast but only outputs text files of various format (fixed, CSV, tab-delimited) which you can put into SQL Server with bcp or BULK INSERT. I did use this for some automated import because it was the least hassle and also the fastest method. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-09 : 22:09:25
|
| I always had the best luck with the client access oledb driver.It's rather easy once you have the right connection string.so...what driver are u using? How do you connect to the 400 now?Please see here: http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzaij/rzaijinstallxe1.htm |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-08-12 : 10:33:36
|
| thanks the website helped alot! :) |
|
|
|
|
|
|
Export from AS400