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
- Create a sequence object in Microsoft SQL Server.
- Create a Connect-SQL to access it.
- Call Connect-SQL rule from an activity using RDB-Open.
- 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.
Published November 3, 2017 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.