Skip to main content
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.
LinkedIn
Copied!

Table of Contents

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

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 ledor-Purch@pegasystems.com - 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)

 

Suggest Edit
Did you find this content helpful?

Have a question? Get answers now.

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

Ready to crush complexity?

Experience the benefits of Pega Community when you log in.

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

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us