Question
Exception raise and error handling with SQL Stored Procedure (SQL Server)
Hi all,
We've been having difficulty on Pega being able to get the exception thrown during an execution of an stored procedure using SQL connect.
We've already ran some tests using Obj methods, RDB methods as well. The stored procedure call works fine, however the problem is whenever we have an exception during the execution (for example, a duplicate key insertion attempt) Pega is not able to catch the error so we can perform the proper exception handling.
Is there any best practices or recommended approach to handle exception when we have a transactional call of an stored procedure which is inserting data in some Customer Data tables on the DB Server?
Hi Venkata, thanks for your response.
I've checked with the other developers, and they've confirmed we did try that, however the RAISEERROR we use in our procedure is not populating the Data Page you mentioned.
Here is an example of the exception handling we have in our SQL Stored procedure:
COMMIT TRAN END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); ROLLBACK TRAN; RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH;
can your try using sql error page in connect-sql rule. I think it will hold even DB side errors .
{SQLPage:MySqlErrorsPage}
In this page, the properties holding the error information are: