MSSQL Sequence is not working in connect sql

General
, ,
1

MS SQL Sequence is not working in connect sql

When we give the sequence name directly it is working fine in Connect SQL

SELECT NEXT VALUE FOR [Seq_Person] AS “.pyID”; working

but if changing the sequence name dynamically it is failing with syntax error

SELECT NEXT VALUE FOR {.pyNote} AS “.pyID”;

[MSG][Error in RDB-Open][STACK][com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 102 SQLState: S0001 Message: Incorrect syntax near ‘@P0’. DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ‘@P0’. | SQL Code: 102 | SQL State: S0001 From:

2

@Mukesh-Agrawal

Please try add page name like bellow

SELECT NEXT VALUE FOR {[YourPageName].pyNote} AS “.pyID”;

3

@Mukesh-Agrawal Please find below link for your reference. We have to specify as {ASIS:PageName.SQLQuery} to refer dynamically.

Pass SQL Scripts dynamically to CONNECT SQL | Collaboration Center (pega.com)

4

@LANH（ラン）

The property is already part of primary page and getting transformed with sequence name , but still failing.

5

@KotaC382 Thanks , but only sequence name need to be dynamically sent , not the complete query

6

will update on workaround which I am trying on this :-

  1. create a stored procedure with input as sequence name and output as number and call the sequence inside it..

  2. call stored procedure in Connect-SQL

7

@Mukesh-Agrawal

CREATE PROCEDURE [dbo].[GetSeqNextValue] (@SeqName varchar(50), @NewNum bigint output)

AS

BEGIN
Declare @SQL Nvarchar(1000)

Set @SQL = ‘SELECT @NewNum = Next Value for ’ + @SeqName
Exec sp_executesql @Sql,N’@NewNum bigint output’,@NewNum output

END

and it working…