I've found a way to fail a SQL Scheduled Job (with severity 16) that does not report failure (and so does not send email notifications). I've fixed my immediate issue, but I want to know why there is a failure case that does not report as failure, and if there are any other surprising ways to miss notification.
I've set up two linked servers and am attempting to run an hourly scheduled SQL Job on one that queries the other. I found this morning that the code in the SP had not been running, but the history on the Job was reporting success. The Job's only step is EXEC _testSP. If I ran EXEC _testSP in a query window from SSMS, I received this error message:
Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
The SP's contents are wrapped in TRY ... CATCH. If I remove the TRY ... CATCH, executing the SP gives up this error message:
Msg 213, Level 16, State 7, Line 1 Insert Error: Column name or number of supplied values does not match table definition.
This made sense. The remote table was referenced with a SELECT * FROM and some columns had been added to it. I've removed the asterix and the job runs fine now, but I want to make sure that all future exceptions get logged either by the job failure notification, or the CATCH block in _testSP. I don't understand why this one didn't get logged, and I hope that someone can explain it to me.
The job runs and fails and notifies just as I would expect when the TRY ... CATCH wrapping is removed, but we have some important things in the TRY ... CATCH that need to be kept.
This is not a duplicate of this related question. The Microsoft BOL for TRY...CATCH says that some exceptions cannot be caught by TRY...CATCH. It may be related, but what I've found is an exception that is not caught by the Scheduled Job agent.
Reproduceable example: (also try removing the TRY...CATCH wrapper and see the change)
USE [RemoteServer].[Database]
CREATE TABLE [Tally](
[ID] [int] IDENTITY(0,1) NOT NULL,
[ID2] [int] NOT NULL
) ON [PRIMARY]
GO
USE [LocalServer]
-- Setup procedure
CREATE PROCEDURE _testSP
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
-- Create destination temp table
CREATE TABLE #tempb (a int)
-- Insert into temp table from remote Tally table
DECLARE @query nvarchar(4000)
SELECT @query = '
SELECT TOP 5 *
FROM [Database].[dbo].Tally
'
INSERT INTO #tempb
EXEC [RemoteServer].[master].[dbo].sp_executesql @query
END TRY BEGIN CATCH
-- Rethrow the exception
DECLARE @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
SELECT
@ErrorMessage = 'Handled Exception: ' + ERROR_MESSAGE() + ' line ' + CAST(ERROR_LINE() as nvarchar(5)),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
END
GO
-- Setup job
DECLARE @database varchar(100)
SELECT @database = DB_Name()
EXEC msdb.dbo.sp_add_job
@job_name = '_testSPJob'
EXEC msdb.dbo.sp_add_jobstep
@job_name = '_testSPJob',
@step_name = '_testSPJob',
@subsystem = N'TSQL',
@command = 'EXEC _testSP',
@database_name = @database
EXEC msdb.dbo.sp_add_jobserver
@job_name = '_testSPJob',
@server_name = @@SERVERNAME
GO
-- Manual execution fails
EXEC _testSP
GO
-- Run job
EXEC msdb.dbo.sp_start_job
@job_name = '_testSPJob'
WAITFOR DELAY '00:00:02'
GO
-- Select job history
SELECT * FROM msdb.dbo.sysjobhistory
WHERE step_name = '_testSPJob'
ORDER BY run_date, run_time
GO
I really need to convince the bosses to get off SQL 2000. Here are my software versions. Perhaps this is fixed in later versions of SQL?
SSMS Version: 2012 (11.0.5058.0)
Local DB: SQL 2005 (9.0.5069)
Remote DB: SQL 2000 (8.0.760)
Aucun commentaire:
Enregistrer un commentaire