Question
moj.gov.sa
GB
Last activity: 20 Feb 2022 16:44 EST
MSSQL Sequence is not working in connect sql
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:
Accepted Solution
moj.gov.sa
GB
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...
Updated: 17 Feb 2022 21:49 EST
Toyota System
JP
Please try add page name like bellow
SELECT NEXT VALUE FOR {[YourPageName].pyNote} AS ".pyID";
Cognitonic Systems Australia
AU
@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)
moj.gov.sa
GB
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
Accepted Solution
moj.gov.sa
GB
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...