Import from as/400 to sql server 2000 database 2

[scotitpulsa]

We have an as/400 that i need to export information from, or import (insert) into a sql server 2000 database. Has anyone done this via an import or extraction using sql server 2000? Could someone point in the direction to take when making the attempt to do this? Thanks in advance!

Scot

 

[gradley]

If you don't have the proper network connections in place where you can create a linked server between the AS/400 ans SQL Server 2000, I would suggest just creating .csv export files from the AS/400 tables and then importing them using either the DTS wizard or a DTS package if alot of data massaging is required. You might find this quicker anyway.

 

[scotitpulsa]

we have them both connected on an internal network. wouldn't an export to a csv sheet be redundant? Wouldn't it just be easier to port directly from the as/400 to the sql database? The procedure, or way to do it is what I am essentially looking for (or pointers).

 

[ecobb]

As gradley mentioned, you can establish a Linked Server in SQL Server to the AS/400 and then use Query Analyzer to pull whatever you need from the 400.



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[scotitpulsa]

i didn't read gradley's close enough. Thanks for the help!!!

 

[gradley]

If you have an OLEDB or ODBC connection string for the As/400, you can create a linked server from the Security folder of Enterprise Manager.

Once the connection is established, you can reference the AS/400 table with a 4 part notation LinkedServerName...TableName

Ex:

INSERT INTO <SQL Table>
(field names...)
SELECT <field names> from LinkedServerName...TableName

 

[leveyp]

Use IBM i-series access to create an ODBC data source. Works fine - I use it all the time. IBM DB2 Client Connectivity also fine but more expensive if you have got a licence already.