SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Updating AS/400 Linked Server Tables from SQL Server

By Basu Hullur, (first published: 2006/04/05)

Recently, a project required that after downloading data from AS/400 tables to load a data warehouse, a table in AS/400 be updated with the time stamp to indicate when the load process started and when it completed. These time stamps are then used by the nightly AS/400 process to determine the data for the next load. The data warehouse load process used to be a Java application and the project charter was to eliminate the Java program and move the entire processing to SQL Server 2000 and make use of the linked server to AS/400. The Java program used to read the data one row at a time per table and then either insert or update based on the Add/Update indicator. Obviously it was a slow process.

Here are some details on the new linked server setup:

The AS/400 connection on the SQL server machine is defined as a system DSN named AS400_DEV using the iSeries Access ODBC Driver. Using the DSN, a linked server named AS400 is defined on the SQL Server to connect to AS/400 tables as show below with proper security configuration.

AS/400 Linker Server Defination
AS/400 Linker Server Definition

Now it is easy to refer to AS/400 tables using the four part naming convention and get data from the AS/400 tables. Here is an example:

    	CountryNbr = E7CY#,
    	NameCountryShort = E7CYSN,
    	NameCountry = E7CYFN,
    	AddUpdateInd = E7ADUP
But when you try to update a field in the AS/400 table, with a statement such as

SET ZZSSRV = '2006-02-08 09:00:19'

the following error was encountered though the user was given CHANGE authority to that file in AS/400.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.  
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver]
[DB2 UDB]SQL7008 - WSZZP100 in NSCDEVDATA not valid for operation.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData returned 0x80004005:   ].

The search on the web returned thousands of results indicating that many people were having issues updating AS/400 tables, but in many cases there were no responses and no complete solution was available. Finally, I ended up on a page that had some useful information. The link to that page is given here. The alternate solution suggested was to use a DTS package with an ODBC connection to AS/400 and executing the SQL statement to get the update done. The following screenshot shows the details of the DTS package:

DTS Package Details
DTS Package Details

The AS/400 connection is defined as other ODBC connection as shown below:

AS/400 Connection Details
AS/400 Connection Details

The Execute SQL task has the update statement as shown below:

Execute SQL task Details
Execute SQL task Details

Here is the actual statement for clarity:


Please note that once the connection is set to AS/400 in the execute SQL task, TSQL is no longer valid and the SQL statement needs to conform to the IBM DB2 SQL standards. Here is the link for DB2 Universal Database for iSeries SQL Reference Guide

This DTS can be executed within a stored procedure to update the beginning timestamp and another similar DTS package was created and executed from the stored procedure when the load process was complete.

Apparently there is no valid reason why the AS/400 linked server tables cannot be updated, deleted or inserted from SQL Server. The experts at Microsoft and IBM can work on that problem. It would be interesting to see whether this issue is resolved in SQL Server 2005. Meanwhile, it is always good to have an alternative approach in the tool bag to get the job done.

Basu Hullur
Encore Consulting Services, Inc

Total article views: 42567 | Views in the last 30 days: 1
Related Articles

Linked Server from SQL SERVER 2005 to AS/400

Linked Server from SQL SERVER 2005 to AS/400


Linker server to AS/400 DB2 - character translation problems

LinkeD server returning garbage for special characters on AS/400 DB2


importing data from as/400 to sql server 2005

importing data from as/400 to sql server 2005 using


Create Linked Server from SQL Server 2005 to AS/400

How to Create Linked Server from SQL Server 2005 to AS/400


How do I call query on linked AS/400?

Call query on AS/400 from SQL Server 2005 *SOLVED*