I'm trying to update a table on an iSeries (AS400) within a SQL Server stored procedure using linked server, but it is failing when trying to update a column defined as zoned.
In my stored procedure the value is defined as integer, and on the iSeries is defined as Zoned 6 long with no decimals.
I've tried casting the integer as follows:
cast(@SysDate as NUMERIC(6))
but I'm still getting error:
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "PD999 F98OWSEC" could not UPDATE table.
The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.
I don't get this error when removing the zoned fields from the update so I'm pretty sure it's a type conversion issue.
Linked server is defined as OLE DB Provider for ODBC drivers and provider string is MSDASQL.1
Any help here would be greatly appreciated
Best Answer
IBMi DB2 Numeric Data Types:
RPGLE - DESCRIPTION - DB2 SQL
P - Packed decimal - DECIMAL - DEC
S - Zoned decimal - NUMERIC - NUM