Question

11
Replies
450
Views
Brahmesh@ Member since 2013 143 posts
Capgemini
Posted: 8 months ago
Last activity: 8 months 3 weeks ago
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.

pxSQLStatementPreCall usp_skan_case_purge ( {.pzInsKey} , {.pyStatusValue OUT}, {.pyStatusMessage OUT} )
pxSQLStatementPostCall usp_skan_case_purge ( ? , ?, ? )
pxRDBSQLVendorError1102
pxRDBSQLVendorMessage1Incorrect syntax near '@P0'.
pxRDBErrorThere 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
pzStatusvalid

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
Share this page LinkedIn