Community forum

Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Options
View
Go to last post Go to first unread
Offline Thales  
#1 Posted : Tuesday, July 30, 2019 9:57:23 PM(UTC)
Thales

Rank: Paid support

Joined: 5/4/2018(UTC)
Posts: 4
Canada
Location: Ontario, Toronto

Thanks: 2 times
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)?

Edited by moderator Friday, August 2, 2019 8:30:39 PM(UTC)  | Reason: Not specified

Online thomas  
#2 Posted : Wednesday, July 31, 2019 7:41:51 AM(UTC)
thomas

Rank: Paid support

Joined: 12/10/2013(UTC)
Posts: 442
Norway

Thanks: 21 times
Was thanked: 170 time(s) in 129 post(s)
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;

Edited by user Wednesday, July 31, 2019 7:46:35 AM(UTC)  | Reason: Not specified

thanks 2 users thanked thomas for this useful post.
Support on 7/31/2019(UTC), Thales on 8/2/2019(UTC)
Offline Thales  
#3 Posted : Friday, August 2, 2019 6:25:42 PM(UTC)
Thales

Rank: Paid support

Joined: 5/4/2018(UTC)
Posts: 4
Canada
Location: Ontario, Toronto

Thanks: 2 times
Thank you Thomas, the issue is now resolved!
Users browsing this topic
Guest
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Scroll to Top