SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Issues performing INSERT on AS/400 Linked Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Scottee25
Starting Member

3 Posts

Posted - 02/02/2007 :  12:27:24  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 02/07/2007 :  01:24:52  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

hjyoungii
Starting Member

USA
3 Posts

Posted - 05/15/2007 :  09:43:18  Show Profile  Visit hjyoungii's Homepage  Reply with Quote
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.
Go to Top of Page

Andres95
Starting Member

USA
1 Posts

Posted - 11/13/2007 :  08:49:14  Show Profile  Reply with Quote
Maybe this help you...

http://www.geocities.com/a_n_d_r_e_s_g_l_z/SQLServer/AS400_by_SQLServer_LinkedServer.htm
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000