I am trying to set-up a linked server in SQL Server 2000 to our AS400 using client access. After setting up the linked server I can select from a table on the 400 in query analyzer using the following syntax:

Select * from AS400.S1042A6M.QGPL.CTELAB_1

This works fine. I receive the rows in the table on the AS400.

When I try to insert records using the same syntax it fails:
Insert into AS400.S1042A6M.QGPL.CTELAB_1(PER_ID,ACCTNUM,JDEABNUM,USERID)
Values ('00015096513511D696FB000103147803','LJS00000074460','90076833','CtelUser')

Here is the error message that I receive:
Server: Msg 7399, Level 16, State 1, Line 4
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL7008 - CTELAB_1 in QGPL not valid for operation.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IOpenRowset::OpenRowset returned 0x80004005: ]


Has anyone been able to successfully set-up a linked server using client access to an AS400 and if so, what am I doing wrong.




MS Sql Server LazyDBA home page