Linked server error trying insert to AS400 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Linked server error trying insert to AS400

Hi, We have setup a linked server(sql server 2000 sp3) to our AS400 and we are able to run select statements against it successfully, however when we try to run an insert we get an error. Here is the insert we are using and error that we are getting. Thanks in advance for any help. Insert: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
insert openquery(as400ppco1,’select AMCRSTNAM, AMCRSTVAL from AM_CURRUN’)
select ‘Test’,’Test’ Error: Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider ‘MSDASQL’ reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL7008 – AM_CURRUN in FILES not valid for operation.]
OLE DB error trace [OLE/DB Provider ‘MSDASQL’ IRowsetChange::InsertRow returned 0x80004005: ]. ODBC version 8.0
Client Access version 5 release 1 Thanks,
Troy Kelly
What is the version of ODBC and Client Access express you’re using?
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
You can’t insert into an OPENQUERY… You’d need to use a four part name as in: insert into as400ppco1.<db>.<owner>.AM_CURRUN(
AMCRSTNAM,
AMCRSTVAL )
select ‘Test’,’Test’ Cheers
Twan
]]>