I have a stored procedure that is executed via a SQL job. I am trying to output my date range for each execution of my WHILE loop to a log file so that the progress can be monitored outside of SQL Server (this step of the job can take up to 4 hours to run, as it executes per week for a 4-year period). To do so, I've set up the RAISERROR statement at the beginning of each loop:
DECLARE @START_DATE date,
@END_DATE date,
@DATE_RANGE nvarchar(255)
SELECT @START_DATE = '20231029',
@END_DATE = DATEADD(day, 6, @START_DATE)
BEGIN TRY
WHILE @END_DATE <= CAST(GETDATE() as date)
BEGIN
SET @DATE_RANGE = 'Start date: ' + CAST(@START_DATE as nvarchar(10)) + '.....End date: ' + CAST(@END_DATE as nvarchar(10)) + '(' + CAST(GETDATE() as nvarchar(50)) + ')'
RAISERROR(@DATE_RANGE, 2, 1) WITH NOWAIT
-- INSERT statement to execute
END
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(MAX),
@ErrorSeverity int,
@ErrorState int
-- Get the current error
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
RAISERROR (@ErrorMessage, -- Message text
@ErrorSeverity, -- Severity
@ErrorState -- State
)
END CATCH
This works perfectly fine until the very last execution of the WHILE loop, at which point the SQL job fails with the following error:
Step ID 1
Server <my server>
Job Name Process Historic Invoices
Step Name Collect Historic Invoices
Duration 00:02:05
Sql Severity 2
Sql Message ID 50000
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: <domain service account user>. Start date: 2023-10-29.....End date: 2023-11-04(Nov 6 2023 6:27AM) [SQLSTATE 01000] (Error 50000). The step failed.
After looking around for ERROR 50000, I've come to discover this is being thrown because of my RAISERROR at the beginning of my WHILE loop. I commented out my RAISERROR, and executed the job again. The job step completed successfully, as expected.
Is there any way to clear out RAISERROR at the end of my WHILE loop so that the job step can report success, and the rest of the job can continue?
Severity 10 or lower errors are not transferred to the catch block and are returned to the caller. The caller in this case is the agent and it will consider severity 2-9 errors as a job failure while severity 0-1, 10 are job success. This does allow you to have a non-fatal error in T-SQL be either a job failure or not. Error handling in T-SQL has never been easy even without the agent adding its own twist.
Chad Baldwin has a very nice table describing this at the following link.
https://chadbaldwin.net/2021/01/15/raiserror-cheatsheet.html