Skip to main content


         This documentation site is for previous versions. Visit our new documentation site for current releases.      
 

This content has been archived and is no longer being updated.

Links may not function; however, this content may be relevant to outdated versions of the product.

Issue: SQLState: 50002 Arithmetic Overflow error for data type tinyint with SQL Server 2005, JDBC 2.0sppr_rr_class_filtered

Updated on September 10, 2021

Symptom

Your Process Commander deployment uses Microsoft SQL Server 2005 JDBC driver version 2.0. The Process Commander logs show the SQLState S0002 Message: Arithmetic overflow error for data type tinyint. You can encounter this error in a variety of contexts: when copying a rule from a locked RuleSet version to the active version or during process flow transactions.

 

Example

Here"s an example of a log excerpt:

2009-05-26 12:48:44,077 [         http-8082-3] [  STANDARD] [AtPegaPurchase:01.02] (ne.database.RulesetContextImpl) ERROR lledorxp1|10.60.103.86 [email protected] - There was a problem opening a database instance (class: Rule-Obj-Property): Problem getting candidates for Rule Resolution (class: Rule-Obj-Property, object class: AtPega-Data-Purchase-OrderLineItem-Reports, family name: ATPEGA-DATA-PURCHASE-ORDERLINEITEM-REPORTS!PYDESCRIPTION): code: 220

SQLState: S0002 Message: Arithmetic overflow error for data type tinyint, value = 259.
com.pega.pegarules.pub.database.DatabaseException: Problem getting candidates for Rule Resolution (class: Rule-Obj-Property, object class: AtPega-Data-Purchase-OrderLineItem-Reports, family name: ATPEGA-DATA-PURCHASE-ORDERLINEITEM-REPORTS!PYDESCRIPTION): code: 220

SQLState: S0002 Message: Arithmetic overflow error for data type tinyint, value = 259.
From: (HE48A5D1346E0731AA955440B48A0686E:10.60.103.86)
      SQL: {call sppr_rr_class_filtered(?,?,?,?)}
      SQL Inserts: <Rule-Obj-Property>    <!PYDESCRIPTION>  <AtPega-Data-Purchase-OrderLineItem-Reports,AtPega-Data-Purchase-OrderLineItem,AtPega-Data-Purchase-,PegaFinance-Data-Purchase-OrderLineItem-Reports,PegaFinance-Data-Purchase-OrderLineItem,PegaFinance-Data-Purchase-,PegaFinance-Data-,PegaFinance-Data-LineItem,PegaEnt-Data-,PegaEnt-,Data-,@baseclass>      <pr4_rule_property>    

Caused by SQL Problems.
Problem #1, SQLState S0002, Error code 220: com.microsoft.sqlserver.jdbc.SQLServerException: Arithmetic overflow error for data type tinyint, value = 259.

The error occurs due to a Process Commander limitation. If an activity calls the stored procedure sppr_rr_class_filtered, the Arithmetic Overflow exception occurs because the variable tinyint in the statement declare @class_end_idx tinyint of the stored procedure cannot hold values larger than 255; the value specified in the example is 259.

Solution

Workaround

To prevent the arithmetic overflow error, edit the stored procedure sppr_rr_class_filtered: Change the tinyint variable in the statement declare @class_end_idx tinyint to the smallint variable: declare @class_end_idx smallint.

Resolution

To edit the stored procedure, sppr_rr_class_filtered, complete the following steps:

  1. Open Microsoft SQL Server Management Studio Express.
  2. Open the database used by Process Commander.
  3. Navigate to Programmability > Stored Procedure.
  4. Locate the procedure named dbo.sppr_rr_class_filtered.
  5. Right-click the procedure name to display the context menu and click Modify.

modify stored procedure

  1. The procedure opens to display its syntax, similar to this:

create procedure sppr_rr_class_filtered
@aRuleType varchar(64),
@aInsId varchar(128),
@aClassList varchar(500),
@aTableName varchar(64)
AS
declare @class_begin_idx smallint
declare @class_end_idx tinyint
declare @class_name varchar(64)
declare @SQL nvarchar(1000)
declare @SQLvars nvarchar(1000)
declare @errorState int
declare @err int

  1. Replace the statement declare @class_end_idx tinyint with declare @class_end_idx smallint. The procedure syntax should look similar to this:

create procedure sppr_rr_class_filtered
@aRuleType varchar(64),
@aInsId varchar(128),
@aClassList varchar(500),
@aTableName varchar(64)
AS
declare @class_begin_idx smallint
declare @class_end_idx smallint
declare @class_name varchar(64)
declare @SQL nvarchar(1000)
declare @SQLvars nvarchar(1000)
declare @errorState int
declare @err int

  1. From the menu bar, click Execute.

execute stored procedure

Related Topics

Issue: Out of Memory: Java Heap Space error with SQL Server 2005 JDBC Driver 1.2 when calling stored procedure sppr_rr_class_filtered

Troubleshooting 'SQLException: the server failed to resume' (WebSphere 6.1 and SQL Server)

Microsoft Developer Network Reference

wwwint, bigint, smallint, and tinyint (Transact-SQL)

 

  • Previous topic Issue: Out of Memory: Java Heap Space error with SQL Server 2005 JDBC 1.2 when calling sppr_rr_class_filtered
  • Next topic Issue: Update DB2 z/OS stored procedures sppc_data_uniqueid and UNIQID to prevent infinite loop and improve performance

Have a question? Get answers now.

Visit the Support Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega.com is not optimized for Internet Explorer. For the optimal experience, please use:

Close Deprecation Notice
Contact us