1. CRIAR O OPERATOR

EXEC msdb.dbo.usp_TenantSqlAgentOperator_Create
    @OperatorName = N'NOTIFICATION_OPERATOR',
    @EmailAddress = N'<EMAIL_TESTE>',
    @Enabled      = 1;
GO

2. CRIAR O JOB

EXEC msdb.dbo.sp_add_job
    @job_name    = N'NOTIFICATION_JOB',
    @enabled     = 1,
    @description = N'GUIA - aviso por e-mail na falha';
GO

3. CRIAR UM STEP DE FALHA CONTROLADA

EXEC msdb.dbo.sp_add_jobstep
    @job_name          = N'NOTIFICATION_JOB',
    @step_name         = N'Trabalho',
    @step_id           = 1,
    @subsystem         = N'TSQL',
    @database_name     = N'<MEU_DATABASE>',
    @command           = N'THROW 51000, ''GUIA - falha controlada'', 1;',
    @on_success_action = 1,      -- sucesso: termina o job com sucesso
    @on_fail_action    = 4,      -- falha: vai para o passo indicado abaixo
    @on_fail_step_id   = 2;
GO

/*
No SSMS, o mesmo ajuste fica em Steps > (passo) > Advanced:
    On success action: Quit the job reporting success
    On failure action: Go to step: [2] Avisar por e-mail
*/

4. PASSO DE AVISO

EXEC msdb.dbo.sp_add_jobstep
    @job_name          = N'NOTIFICATION_JOB',
    @step_name         = N'Avisar por e-mail',
    @step_id           = 2,
    @subsystem         = N'TSQL',
    @database_name     = N'msdb',
    @command           = N'
DECLARE @para nvarchar(max) = (SELECT TOP (1) email_address FROM msdb.dbo.sysoperators WHERE name = N''NOTIFICATION_OPERATOR'' AND enabled = 1);
IF NULLIF(LTRIM(RTRIM(@para)), N'''') IS NULL THROW 51000, N''Operador NOTIFICATION_OPERATOR sem e-mail.'', 1;
DECLARE @corpo nvarchar(max) = N''O job NOTIFICATION_JOB falhou em '' + @@SERVERNAME + N'' ('' + CONVERT(nvarchar(19), SYSDATETIME(), 120) + N''). Veja o histórico do job.'';
EXEC msdb.dbo.sp_send_dbmail
     @profile_name = N''<MEU_PERFIL>'',
     @recipients   = @para,
     @subject      = N''[SQL Server Agent] Job NOTIFICATION_JOB falhou'',
     @body         = @corpo;',
    @on_success_action = 2,      -- depois de avisar: termina o job como FALHA
    @on_fail_action    = 2;
GO

5. VINCULAR AO SERVIDOR LOCAL

EXEC msdb.dbo.sp_add_jobserver
    @job_name=N'NOTIFICATION_JOB',
    @server_name=N'(LOCAL)';
GO

6. CONFERIR O DESVIO DE FALHA

SELECT s.step_id, s.step_name, s.on_success_action, s.on_fail_action, s.on_fail_step_id
FROM msdb.dbo.sysjobsteps AS s
JOIN msdb.dbo.sysjobs AS j ON j.job_id = s.job_id
WHERE j.name = N'NOTIFICATION_JOB'
ORDER BY s.step_id;
GO

/*
Resultado esperado:
1 Trabalho           on_success_action=1  on_fail_action=4  on_fail_step_id=2
2 Avisar por e-mail  on_success_action=2  on_fail_action=2
*/

7. TESTE FUNCIONAL

/*
sp_start_job só PEDE a execução; o Agent roda em seguida. Espere alguns segundos antes de consultar.
*/

EXEC msdb.dbo.sp_start_job
    @job_name=N'NOTIFICATION_JOB';
GO

WAITFOR DELAY '00:00:15';

SELECT TOP (10)
    h.step_id,
    h.step_name,
    h.run_status,
    h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j
  ON j.job_id=h.job_id
WHERE j.name=N'NOTIFICATION_JOB'
ORDER BY h.instance_id DESC;
GO


/*
Resultado esperado (medido em 14/10/2026):
    step 1 run_status=0  "GUIA - falha controlada ... The step failed."
    step 2 run_status=1  "Mail (Id: N) queued. ... The step succeeded."
    step 0 run_status=0  "The job failed. ... The last step to run was step 2"
*/

--Conferir o envio (status "sent" em até um minuto):
SELECT TOP (5) mailitem_id, subject, sent_status, sent_date
FROM msdb.dbo.sysmail_allitems
ORDER BY mailitem_id DESC;
GO

8. EXCLUIR OS OBJETOS AUXILIARES

EXEC msdb.dbo.sp_delete_job
    @job_name=N'NOTIFICATION_JOB',
    @delete_unused_schedule=1;
GO

EXEC msdb.dbo.usp_TenantSqlAgentOperator_Delete
    @OperatorName=N'NOTIFICATION_OPERATOR',
    @ConfirmOperatorName=N'NOTIFICATION_OPERATOR';
GO

9. VALIDAÇÃO FINAL

SELECT name FROM msdb.dbo.sysjobs
WHERE name=N'NOTIFICATION_JOB';

SELECT name FROM msdb.dbo.sysoperators
WHERE name=N'NOTIFICATION_OPERATOR';
GO

Resultado esperado: zero linhas.