sql server - SQL Job with TRY reports success on failure when inserting from remote SP -

i've found way fail sql scheduled job (with severity 16) not report failure (and not send email notifications). i've fixed immediate issue, want know why there failure case not report failure, , if there other surprising ways miss notification.

i've set 2 linked servers , attempting run hourly scheduled sql job on 1 queries other. found morning the code in sp had not been running, history on job reporting success. job's step exec _testsp. if ran exec _testsp in query window ssms, received error message:

msg 0, level 11, state 0, line 0 severe error occurred on current command. results, if any, should discarded.

the sp's contents wrapped in try ... catch. if remove try ... catch, executing sp gives error message:

msg 213, level 16, state 7, line 1 insert error: column name or number of supplied values not match table definition.

this made sense. remote table referenced select * from , columns had been added it. i've removed asterix , job runs fine now, want make sure all future exceptions logged either job failure notification, or catch block in _testsp. don't understand why 1 didn't logged, , hope can explain me.

the job runs , fails , notifies expect when try ... catch wrapping removed, have important things in try ... catch need kept.

this not duplicate of this related question. microsoft bol try...catch says exceptions cannot caught try...catch. may related, i've found exception not caught scheduled job agent.

reproduceable example: (also try removing try...catch wrapper , see 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         begin         set nocount on;          begin try          -- create destination temp table             create table #tempb (a int)          -- insert temp table remote tally table             declare @query nvarchar(4000)             select @query = '                 select top 5 *                 [database].[dbo].tally             '             insert #tempb             exec [remoteserver].[master].[dbo].sp_executesql @query          end try begin catch             -- log exception             -- ...              -- rethrow exception             declare @errormessage nvarchar(max), @errorseverity int, @errorstate int;             select                 @errormessage = 'handled exception: ' + error_message() + ' line ' + cast(error_line() 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 * msdb.dbo.sysjobhistory     step_name = '_testspjob'     order run_date, run_time     go 

i need convince bosses off sql 2000. here software versions. perhaps 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) 

i think happening correct, because you're handdling exception, job not failling. solution log when exception y catch insert row error description.