Stored Procedure invoked from PostSQL of Export step is not processing all records
hi,
In my workflow, I am connecting to SQL server 2012 using Custom JDBC jar (Microsoft JDBC Jar 12.2) and the connection string looks like below
jdbc:sqlserver://HOST:PORT;instanceName=MSSQLSERVER;allowPortWithNamedInstance=true;DatabaseName=DBName;LoginTimeout=15;UseServerSideUpdatableCursors=true;SelectMethod=cursor;encrypt=FALSE;connectRetryCount=5;connectRetryInterval=10
I am invoking a stored procedure using the credential created using above connection from the Export step. The Stored procedure is as follows
USE [DBName]
GO
/****** Object: StoredProcedure [Dedupe].[usp_ExperianTest] Script Date: 7/3/2023 3:04:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [Dedupe].[usp_ExperianTest]
AS
BEGIN
SET NOCOUNT ON
TRUNCATE TABLE Dedupe.RunLog
DECLARE @index INT = 1
WHILE @index <= 5
BEGIN
PRINT 'Started'
BEGIN TRAN
BEGIN TRY
IF @index = 2
BEGIN
RAISERROR ('RAISING ERROR',12,1)
END
INSERT INTO Dedupe.RunLog VALUES (@index,'PASS')
END TRY
BEGIN CATCH
PRINT 'Catch Block Error'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_STATE() AS ErrorState,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
-- Need to create transaction so can be commited below
BEGIN TRAN
INSERT INTO Dedupe.RunLog VALUES (@index,'ERROR')
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRAN
WaitFor Delay '00:00:10.000'
SET @index = @index + 1
END
END
When invoking the same procedure from SSMS, I could see the Dedupe.RunLog table getting updated for all 5 ids, However, when Experian workflow with above export step is executed, although no error is thrown, the Dedupe.RunLog table is loading ids 2 till 5 and skipping for Id 1.
We have seen similar issues in other stored procedures as well where Experian Workflow is completing the export step when the stored procedure throws an error and control goes to the Catch block. Even if the stored procedure is designed to handle every error in the loop, Experian is exiting whenever error is thrown and procedure also stops processing any further.
Please suggest how can we resolve this from Experian.
Answers
-
Hi, regarding this comment "Experian Workflow is completing the export step when the stored procedure throws an error"
Do you have this option selected?
Also do you ever run into similar issues doing an INSERT without the stored procedure?
0 -
@Shreya The problem seems to be to do with how transactions are being committed in your SP. When I added an additional transaction commit at the start of the WHILE loop, I see the expected behaviour (the TRUNCATE also succeeds)
...
DECLARE @index INT = 1
WHILE @index <= 5
BEGIN
PRINT 'Started'
-- Commit outstanding transactions
IF @@TRANCOUNT > 0
COMMIT TRAN
BEGIN TRAN
BEGIN TRY
...1 -
Thanks @Henry Simms Since SSMS execution was working as expected we did not anticipate the issue in the SP …however this approach in SP helped solve the issue and now both SSMS and Experian execution are giving expected response.
1 -
Hi @Josh Boxer … thanks for the question… We did not face this issue while doing an Insert/Update. Also we did try with checking/unchecking the 'Continue Workflow execution on JDBC Exports Error' but the result were same.
After Implementing the changes as suggested by @Henry Simms , we are able to see expected SP execution results and as per end to end processing logic, we have enabled error handling for any workflow failure so we would not need to check the 'Continue Workflow execution on JDBC Exports Error' .
0
