Transferring data from AS400 to SQL Server

Question

Transferring data from AS400 to SQL Server

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 739545 More actions · Answer 1

Install the Client Access drivers on the SQL machine and it should work fine. We had linked servers from SQL2K to AS/400 and people could query through SQL Server and get data back from the AS400.

Only issue was that sometimes the AS/400 driver would leak memory and then stop returning full result sets. We would be limited to xx bytes, which made for difficult troubleshooting. Watch for issues with the links, like the DTS failing sometime.

Loner SSC-Insane Points: 21279 More actions · Answer 2

I used DTS package to get data from AS400 to SQL Server 2000. It seems to work fine.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 3

I'll second Steve here and reccpment Client Access and avoid Client Access Express. The full version is worth the extra money in a production environment.

Remeber that there are some T-SQL differences between SQL Server and DB-400. Google OPENROWSET 400.

ATBCharles Kincaid

thierry Old Hand Points: 301 More actions · Answer 4

I transfer datas from AS400 to SQL Server 2005 via SSIS, it works well but there is a bug in V5R3 that disable the possibility to make SQL querys against AS400.

It is only possible to transfer whole tables.

This bug is known by IBM and the V5R4 fixed this problem. But in my case, it is not planned to install V5R4 on our AS400s, so, I'm in an impass...

http://www-1.ibm.com/support/docview.wss?uid=nas2a802b6eb29d32cac8625726c0041efc9

Jartram Newbie Points: 7 More actions · Answer 5

Im doing the same thing. I need to migrate all data from AS400 to SQL, I get to connect to AS400 via Client Access, but at this point im just getting data from QGPL Library, i would like to know how to configure the client access to get data from all libraries.

Thanks!

Charles Kincaid SSChampion Points: 13593 More actions · Answer 6

It's been a lot of years since I delt with 400's. What I remember is that you should get a library list from your logon. If your libraries are not in the logon list you can specifiy a new library list in Client Access. I remember having to do it but don't remember how. Check the Client Access documentation. I hate saying RTM but sometimes the manual helps.

ATBCharles Kincaid