Stored Procedure invoked from PostSQL of Export step is not processing all records

Shreya

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.

Josh Boxer

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?

Henry Simms

@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

    ...

Shreya

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.

Shreya

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' .