Question

11
Replies
688
Views
Close popover
Brahmeswara Rao (Brahmesh@)
Capgemini
Developer
Capgemini
SE
Brahmesh@ Member since 2013 151 posts
Capgemini
Posted: November 7, 2019
Last activity: November 12, 2019
Closed
Solved

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;

Data Integration
Moderation Team has archived post,
Close popover This thread is closed to future replies. Content and links will no longer be updated. If you have the same/similar Question, please write a new Question.