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)?

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;

Thank you Thomas, the issue is now resolved!