Issues performing INSERT on AS/400 Linked Server

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Issues performing INSERT on AS/400 Linked Server

Author

Topic

Scottee25
Starting Member

3 Posts

Posted - 2007-02-02 : 12:27:24

Hello,

I am new to the forum so please forgive me if I have not posted this in the correct location.

I am encountering an issue when attempting to perform an INSERT against an AS/400 Linked server. From what I have been able to find via searches, others have encountered the same issue as I have, and while some of them have been able to resolve their problem using the suggestions posted throughout the web, I am apart of the group that has been unable to achieve resolution.

We currently have a System DSN pointing to our AS/400 DB and have set the DSN to allow Read/Write, as well as, setting Commit Mode to Commit Immediate(*NONE) (Having this set to anything other than NONE without Journaling on the tables appears to be the biggest reason why people are unable to write to their AS/400 tables). The DSN uses the "Client Access Express ODBC data source" driver.

Using this System DSN we ARE able to both read and write to this AS/400 database using a DTS, as well as, via a linked server in MS Access 2003. However, we are unable to accomplish writes to the DB via our Linked Server in SQL Server. In fact, we are unable to even query the tables in AS/400 when using OPENDATASOURCE or four part naming. OPENROWSET and OPENQUERY both work for us when doing SELECT queries.

Now when we created our Linked Server in SQL Server, we used the Provider "Microsoft OLE DB Provider for ODBC Driver" and set the DataSource to our System DSN(The Client Access Driver is not available to us in this drop down). On the Security tab we set the remote login and password. On the Server options tab we have only the "Data Access" and "Use Remote Collation" options set.

When we attempt to perform the following SELECT queries using either the OPENDATASOURCE or four part naming convention:
SELECT * FROM AS400.EWCO.JBSTRNDB.ZPISSUE

SELECT *
FROM OPENDATASOURCE(
'MSDASQL',
'Data Source=JOBSCOPETESTLIB;User ID={USERID};Password={PASSWORD};'
).EWCO.JBSTRNDB.ZPISSUE

({USERID} and {PASSWORD} have simply been replaced here for security purposes)

We get the error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object '"EWCO"."JBSTRNDB"."ZPISSUE"'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='MSDASQL', Query="EWCO"."JBSTRNDB"."ZPISSUE"'].

However when we perform the following SELECT queries, all works fine:
SELECT A.*
FROM OPENROWSET('MSDASQL', 'DSN=JOBSCOPETESTLIB;UID={USERID};PWD={PASSWORD}', 'SELECT * FROM EWCO.JBSTRNDB.ZPISSUE') AS A

SELECT *
FROM OPENQUERY(AS400, 'SELECT * FROM EWCO.JBSTRNDB.ZPISSUE ')

(Once again, {USERID} and {PASSWORD} have simply been replaced here for security purposes)

Preferably, we would like to use the four part naming on the INSERT but since that will not work for us, we thought we would try one of the two methods we used in our SELECT queries that did work. After much research, an article I found at Microsoft suggested using syntax similar to the following:

INSERT INTO OPENQUERY(AS400, 'SELECT ZIREL FROM EWCO.JBSTRNDB.ZPISSUE WHERE 1=0') VALUES ('TESTJOB')

However, when I attempt this, I get the following error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL7008 - ZPISSUE in JBSTRNDB not valid for operation.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005: ].

All I have found regarding this error leads me back to the Commit Mode and Journaling. As I stated earlier, we have our Commit Mode set to Commit Immediate (*NONE) in our System DSN and this does allow use to write to the table via Linked Tables in Microsoft Access, as well as, in a DTS. For the life of us, we cannot figure out how to accomplish the writing to a table on our AS/400 via SQL. Has anyone else encountered this and found a solution? We would prefer to do this with SQL since we want to place it in a trigger. Executing a DTS from a trigger has proven to be problematic. We do have some other alternatives we can use, but they will not allow for real-time processing which is our ultimate goal.

Can anyone please offer any suggestions?

Thank you,

Scott

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-02-07 : 01:24:52

as far as i know, cant use odbc (anymore). use client access oledb driver. what is version/revison of OS/400? make sure u have latest windows service pack for client access applied to mssql server.

hjyoungii
Starting Member

3 Posts

Posted - 2007-05-15 : 09:43:18

We too use an AS400. Have you tried something like this? CMS0H.S11F123D.CMSMPA.FFASS
First part is the link server setup for the 400, second is the 400 serial, Third is the database, and finally the table.

Andres95
Starting Member

1 Post

Posted - 2007-11-13 : 08:49:14

Maybe this help you...

[url]http://www.geocities.com/a_n_d_r_e_s_g_l_z/SQLServer/AS400_by_SQLServer_LinkedServer.htm[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources