SQL task does not return error - VisualCron - Forum

Community forum

Thales
2019-07-30T19:57:23Z
Hello,

I crated the following SQL task that executes a stored procedure:
execute [E2].[dbo].[Read_AD];

These are the contents of the stored procedure:

begin try
begin transaction [Tran1];
---- code here
commit transaction [Tran1];
end try

begin catch
IF @@TRANCOUNT > 0 rollback transaction [Tran1];
end catch;

The problem is that when I run the job, no error is returned even though the stored procedure returns an error code. How do I instruct VisualCron to return an error message if the return value of the procedure is not 0 (zero means success)?
thomas
2019-07-31T05:41:51Z
Two things:

1) You should not write exec proc. There is a separate tab for execution of procs. Use that

2) Your code has a bug, it's missing 'throw'. You are catching and rolling back without rethrowing, thus swallowing the error. Below is the proper structure for an sql server proc. Use this for all procs that contain transactions, and make sure you add the 'SET' options also. Read E. Sommarskogs three part series if you want a lot of info 🙂 http://www.sommarskog.se/error_handling/Part1.html 

CREATE PROCEDURE dbo.p_procname(-- Params)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN TRY

-- Declare local variables
BEGIN TRANSACTION;
-- Logic
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@trancount > 0
BEGIN
ROLLBACK TRANSACTION;
END;
THROW;
END CATCH;
Thales
2019-08-02T16:25:42Z
Thank you Thomas, the issue is now resolved!
Scroll to Top