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.

6. 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

7. 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

8. From the menu bar, click Execute.

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

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