Question

Unable to call SQL Server store procedure from Pega.

Unable to make call to store procedure of Sql server 2016..

No issue with executing the store proc directly in sql server studio.

pxSQLStatementPre Call usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT} )
pxSQLStatementPost Call usp_skan_case_purge ( ? , ?, ? )
pxRDBSQLVendorError1 102
pxRDBSQLVendorMessage1 Incorrect syntax near '@P0'.
pxRDBError There was a problem performing a database query: 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
pzStatus valid

Pega 7.3.1

Store procedure :-

CREATE PROCEDURE usp_skan_case_purge
@case_ins_key VARCHAR(128),
@error_status VARCHAR(32) output,
@error_message VARCHAR(1024) output
AS
BEGIN

BEGIN TRY
BEGIN TRANSACTION;

DECLARE @PYID VARCHAR(16);

/* Get pyID from pzInsKey as what comes after first space * 
SET @PYID = SUBSTRING(@case_ins_key, CHARINDEX(' ', @case_ins_key) +1, DATALENGTH(@case_ins_key) - CHARINDEX(' ', @case_ins_key) +1 );

/* Application specific DML queries */

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF (Xact_state()) = -1
BEGIN
ROLLBACK TRANSACTION;
END;

IF (Xact_state()) = 1
BEGIN
COMMIT TRANSACTION;
END;

SET @error_status='Error';
SELECT @error_message = ERROR_MESSAGE();
THROW;

END CATCH

END;

Correct Answer
November 11, 2019 - 10:21am

A I mentioned above, the syntax MUST be (including symbols \{ and \}):

\{Call SCHEMA_NAME.usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT} )\}

Comments

Keep up to date on this post and subscribe to comments

November 8, 2019 - 1:51am

db procedure is fine, could you please provide an sql query sending from pega?

November 8, 2019 - 3:18am
Response to vaspoz

Thanks for response .Below are the query & error details .

pxSQLStatementPre Call usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT} )
pxRDBSQLVendorError1 102
pxRDBSQLVendorMessage1 Incorrect syntax near '@P0'.
pxRDBError There was a problem performing a database query: 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
pzStatus valid

 

November 8, 2019 - 5:44am
Response to Brahmesh@

hm, interesting. How do you make a call, through sql connector?

November 10, 2019 - 9:59am
Response to vaspoz

Yes ..using RDB-List method ( Connect-Sql).

November 11, 2019 - 3:24am

Firstly, I think you should use RDB-Save.

In Save your syntax must be like:

\{Call SCHEMA_NAME.usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT} )\}

 

November 11, 2019 - 6:28am
Response to vaspoz

Thanks for response , still getting the issue ,seems query parsing is not happening as expected.

Trying to call below syntax from RDB-Save.

Call pegaDATA.usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT}

 

 

Error in RDB-Save 
com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 102 SQLState: S0001 Message: Incorrect syntax near '.'. 
DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '.'. 
 | SQL Code: 102 | SQL State: S0001 
 
	at com.pega.pegarules.data.internal.access.ExceptionInformation.createAppropriateExceptionDueToDBFailure(ExceptionInformation.java:354) ~[prprivate.jar:?] 
	at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:333) ~[prprivate.jar:?] 
	at com.pega.pegarules.data.internal.access.Lister.convertSqlExceptionToDatabaseException(Lister.java:577) ~[prprivate.jar:?] 
	at com.pega.pegarules.data.internal.access.Lister.listWithResultPackager(Lister.java:421) ~[prprivate.jar:?] 
	at com.pega.pegarules.data.internal.access.Lister.list(Lister.java:185) ~[prprivate.jar:?] 
	at com.pega.pegarules.data.internal.access.DBQueryExecutor.executeRDB(DBQueryExecutor.java:123) ~[prprivate.jar:?] 

 

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '.'. 
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259) ~[mssql-jdbc-6.4.0.jre8.jar:?] 
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547) ~[mssql-jdbc-6.4.0.jre8.jar:?] 
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:548) ~[mssql-jdbc-6.4.0.jre8.jar:?] 
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:479) ~[mssql-jdbc-6.4.0.jre8.jar:?] 
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7344) ~[mssql-jdbc-6.4.0.jre8.jar:?] 
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2713) ~[mssql-jdbc-6.4.0.jre8.jar:?] 

November 11, 2019 - 6:37am
Response to Brahmesh@

could you attach a screenshot of your rdb-save rule?

November 11, 2019 - 7:53am
Response to vaspoz

 

November 11, 2019 - 10:21am
Response to Brahmesh@

A I mentioned above, the syntax MUST be (including symbols \{ and \}):

\{Call SCHEMA_NAME.usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT} )\}

November 12, 2019 - 3:48am
Response to vaspoz

Thanks it worked ,But curious to know why we need curly brackets {/  /} around query , Is syntax specific to Pega / SQL server ?.

In earlier versions of pega,(7.1.8) ,we used to call store procedure of oracle & postgres with out having curly brackets if i'm not wrong .

why RDB-Save instead RDB-List

November 12, 2019 - 3:52am
Response to Brahmesh@

to be honest, I don't know) but glad that it works