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.

Support Article

SQL Server sequence object incrementing by 2 instead of 1

SA-44561

Summary



User created a sequence object in Microsoft SQL Server which increments by 1 on every access.

At the DB level, verified that the value is only incremented by 1.

But when accessed from Pega7 using connect-sql, the value is incremented by 2.

Example


User created a sequence like this:
CREATE SEQUENCE Test.CounterSeq
AS int
START WITH 1
INCREMENT BY 1 ;
GO

From Connect-Sql, it is accessed like this:

SELECT NEXT VALUE FOR Test.CounterSeq AS FirstUse;
SELECT NEXT VALUE FOR Test.CounterSeq AS SecondUse;

ResultSet in the database:
FirstUse = 1
SecondUse = 2

ResultSet in PRPC:
FirstUse = 2
SecondUse = 4

Connect-SQL is called from the activity using RDB-Open

Error Messages



Not Applicable


Steps to Reproduce

  1. Create a sequence object in Microsoft SQL Server.
  2. Create a Connect-SQL to access it.
  3. Call Connect-SQL rule from an activity using RDB-Open.
  4. Run the activity with tracer on and look at the results.


Root Cause



A third-party product issue. This seems to be an issue with Microsoft SQL Server jdbc driver where selectMethod=cursor connection property results in this behavior.

Sequence is incremented when query is executed and result is retrieved.



Resolution



Perform the following local-change:

It is not recommended to run Pega without selectMethod=cursor connection property but removing this makes sequence increment correctly.

Therefore, i to use Microsoft Sql Server for sequence logic, have a separate Database instance/schema other than PegaRULES datavase where the sequence resides.

This way you can keep selectMethod=cursor parameter in jdbc/PegaRULES database and remove it from jdbc/TestSeq database (where sequence resides) jdbc URL.

Suggest Edit

Published November 3, 2017 - Updated October 8, 2020

Did you find this content helpful? Yes No

100% found this useful

Have a question? Get answers now.

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

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