2

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

Improve this question
4
  • 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.
    – Hannah Vernon ♦
    May 13, 2014 at 14:10
  • @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.
    – Terry
    May 15, 2014 at 8:38
  • 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.
    – Chris Aldrich
    May 25, 2016 at 12:52
  • @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.
    – Terry
    May 27, 2016 at 9:47

1 Answer 1

Reset to default
1

IBMi DB2 Numeric Data Types:

RPGLE - DESCRIPTION - DB2 SQL

P - Packed decimal - DECIMAL - DEC

S - Zoned decimal - NUMERIC - NUM

Improve this answer
1
  • Thanks @ZS6JCE. I've tried both but no luck
    – Terry
    May 15, 2014 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.