AS400 och SQL (engelsk text)

Jag beklagar att nedanstående är på engelska, men det är den information jag har sammanfattat till Microsoft och jag ids inte översätta den... ;)

SQL och AS400 är inte min starka sida, så detta kanske är lätt löst!?

Ok, jepp, det är sant... Rikard har ställt en fråga igen... ;)

8< - - - - - - - - -

SUMMARY:
This issue is regarding replicating data from an AS/400 server to an SQL server. In the end, clients should be able to query the SQL server for information that is actually localized in the AS/400 server. The SQL server databases are updated from the AS400 at certain intervalls via DTS packages using a system-DSN.

INSTALLATION DESCRIPTION:
All products are from Microsoft unless other is specified by brackets - "[manufacturer]".

OS: Windows 2000 AdvSrv SP1 (english, no hot-fixes, no High Encryption)
BackOffice application: SQL server 7.0 SP2
Other applications: [IBM] AS400 Client Access Express V4R4M0 (SF 63193), Access 97*

Installation order:
Windows 2000, [IBM] AS400 Client Access V4R4M0, [IBM] AS400 Client Access V4R4M0 (SF 63193), Access 97*, SQL 7.0, SQL 7.0 SP2, Windows 2000 SP1

The hardware seems quite inrelevant as we have done more or less identical installations on one Compaq ProLiant ML530 - which is meant to be the target server for this solution - and one IBM Workstation. In both cases, the same problem occur.

* for laboration use only.

PROBLEM DESCRIPTION:

Summary:
The ODBC-driver fails to retrieve data from the AS/400 to the SQL server - reporting "Unspecified error". No events written to the system/application log. No events written to the SQL log files.

Detailed description of "successful" attempts:
If, from Access 97, we try to establish a connection using the ODBC driver from Client Access 32, everything runs smoothly. Which means, we are able to connect to the AS/400, extract table information and also retrieve data to an Access database.
This is done by:
1. Launch Access.
2. Create new, blank, database.
3. Link Table (file menu).
4. Choose ODBC.
5. Create New (if configuration is necessary, please notify me).
6. Pick that ODBC connection.
Tables from the AS/400 shows.
7. Creating a query and launching that query.
Information is retrieved and displayed in Access.

Detailed description of "unsuccesful" attempts:
1. Launching Enterprise Manager.
2. Mark the server - in the left pane.
3. Wizards... from the Tools menu.
4. Chosing "DTS Import Wizard".
5. Configuration as follows:
Source: Other ODBC...
User/System DSN: (same choice as för the Access configuration)
Username/password: As required.
NEXT.
Destination: Microsoft OLE DB Provider for SQL Server
Server: servername
Use Windows NT Auth
Database: pubs (this database is used only for testing, it will be changed later on - when this is working... ;) )
NEXT
Use a query...
NEXT
Building a Query using the Query Builder.
When clicking the button "Parse" the following error message is displayed:
"DTS Wizard Error
Unspecified error"
The Q248420 article does not seem to apply in this case as SQL 7.0 SP2 is applied.

REQUIRED OBJECTIVES:

To get this to work. ;)
A working solution has to be implemented before the 14th of February.
It IS necessary to be able to replicate data from AS/400.
It must be a system-DSN.
It's not neccesary to use the Client Access ODBC-driver.
It's not desireable to install Client software on the server - if possible.