Error updating zoned field from SQL Server stored procedure linked server to iSeries

Question

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

• Hannah Vernon: Have you tried using CAST(@SysDate as DECIMAL(6,0)) ? This really should be the same as NUMERIC(6) but its worth a try, I suppose.
• Terry: @MaxVernon thanks for the suggestion. I tried your idea but still no luck. I also tried a constant value of 0 (casting it and not casting) but same error. I'm thinking its a connection config issue but then alpha fields are being updated correctly.
• Chris Aldrich: As odd of a requests as this is.....did the iSeries DBA or operator bind the correct packages on the iSeries? Binding is a big thing in DB2. It basically notifies DB2 of functionality it can perform. This is important with different versions, editions, and platforms of DB2 to talk to each other. We had to bind the right packages on our iSeries, just to allow our Unix versions of DB2 to talk to them. It wouldn't surprise me if the same is true for SQL Server. There may also be a command or config needed from your side to be the equivalent of "binding" from your side.
• Terry: @Chris - thanks for the reply.You may be right. Eventually I avoided the problem by connecting to the iSeries directly from within the C# code using ODBC. Linked Server in MS SQL Server doesn't seem to be able to handle zoned characters where fields of type Graphic also exist.

Answer 1

IBMi DB2 Numeric Data Types:

RPGLE - DESCRIPTION - DB2 SQL

P - Packed decimal - DECIMAL - DEC

S - Zoned decimal - NUMERIC - NUM