O QUE SERÁ CRIADO
=================
- Job:      LAB_R40_JOB
- Step 1:   01 - Consulta
- Step 2:   02 - Segunda consulta
- Agenda 1: LAB_R40_DIARIO
- Agenda 2: LAB_R40_SEMANAL

Somente subsystem TSQL é suportado para o tenant. Não use CmdExec, PowerShell,
SSIS ou Proxy.

1. CRIAR O JOB

EXEC msdb.dbo.sp_add_job
    @job_name    = N'DEV_JOB',
    @enabled     = 1,
    @description = N'LAB 4.0 - Job criado pelo tenant';
GO

--> Consultar
SELECT
    j.job_id,
    j.name,
    j.enabled,
    SUSER_SNAME(j.owner_sid) AS OwnerLogin,
    j.description,
    j.date_created
FROM msdb.dbo.sysjobs AS j
WHERE j.name = N'DEV_JOB';
GO

2. ADICIONAR O PRIMEIRO STEP

EXEC msdb.dbo.sp_add_jobstep
    @job_name          = N'DEV_JOB',
    @step_name         = N'01 - Consulta',
    @step_id           = 1,
    @subsystem         = N'TSQL',
    @database_name     = N'DEV',
    @command           = N'
SELECT
    N''STEP 1''     AS StepExecutado,
    DB_NAME()       AS DatabaseExecutado,
    SUSER_SNAME()   AS LoginExecucao,
    SYSDATETIME()   AS DataHora;',
    @on_success_action = 1,
    @on_fail_action    = 2;
GO

--> Consultar
SELECT
    j.name AS JobName,
    s.step_id,
    s.step_name,
    s.subsystem,
    s.database_name,
    s.on_success_action,
    s.on_fail_action,
    s.command
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobsteps AS s
  ON s.job_id = j.job_id
WHERE j.name = N'DEV_JOB'
ORDER BY s.step_id;
GO

3. CRIAR E VINCULAR A PRIMEIRA AGENDA

EXEC msdb.dbo.sp_add_jobschedule
    @job_name          = N'DEV_JOB',
    @name              = N'LAB_R40_DIARIO',
    @enabled           = 0,
    @freq_type         = 4,       -- diário
    @freq_interval     = 1,
    @freq_subday_type  = 1,       -- uma vez ao dia
    @active_start_time = 230000;  -- 23:00:00
GO

--> Consultar
SELECT
    j.name  AS JobName,
    s.name  AS ScheduleName,
    s.enabled,
    s.freq_type,
    s.freq_interval,
    s.freq_subday_type,
    s.active_start_time
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobschedules AS js
  ON js.job_id = j.job_id
JOIN msdb.dbo.sysschedules AS s
  ON s.schedule_id = js.schedule_id
WHERE j.name = N'DEV_JOB';
GO

4. VINCULAR O JOB À INSTÂNCIA LOCAL

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

--> Consultar
SELECT
   j.name,
   js.server_id
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobservers AS js
 ON js.job_id = j.job_id
WHERE j.name = N'DEV_JOB';
GO

5. EXECUTAR SOB DEMANDA

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

--> Aguarde alguns segundos e consulte:

SELECT TOP (20)
    j.name,
    h.step_id,
    h.step_name,
    h.run_status,
    h.run_date,
    h.run_time,
    h.run_duration,
    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'DEV_JOB'
ORDER BY h.instance_id DESC;
GO

--> run_status:
0 = falha
1 = sucesso
2 = retry
3 = cancelado
4 = em andamento

6. MODIFICAR O JOB

EXEC msdb.dbo.sp_update_job
   @job_name    = N'DEV_JOB',
   @enabled     = 0,
   @description = N'LAB 4.0 - descrição alterada';
GO
SELECT name, enabled, description
FROM msdb.dbo.sysjobs
WHERE name = N'DEV_JOB';
GO
-->Reabilitar:
EXEC msdb.dbo.sp_update_job
   @job_name = N'DEV_JOB',
   @enabled  = 1;
GO

7. MODIFICAR O STEP

EXEC msdb.dbo.sp_update_jobstep
   @job_name      = N'DEV_JOB',
   @step_id       = 1,
   @database_name = N'DEV',
   @command       = N'
SELECT
   N''STEP 1 ALTERADO'' AS StepExecutado,
   DB_NAME()            AS DatabaseExecutado,
   ORIGINAL_LOGIN()     AS OriginalLogin,
   SYSDATETIME()        AS DataHora;';
GO

8. MODIFICAR A AGENDA

EXEC msdb.dbo.sp_update_schedule
   @name              = N'LAB_R40_DIARIO',
   @enabled           = 0,
   @active_start_time = 231500;
GO
SELECT name, enabled, active_start_time
FROM msdb.dbo.sysschedules
WHERE name = N'LAB_R40_DIARIO';
GO

9. ADICIONAR UM SEGUNDO STEP

-->Primeiro, faça o step 1 seguir para o próximo:
EXEC msdb.dbo.sp_update_jobstep
   @job_name          = N'DEV_JOB',
   @step_id           = 1,
   @on_success_action = 3;
GO

-->Agora crie o step 2:
EXEC msdb.dbo.sp_add_jobstep
   @job_name          = N'DEV_JOB',
   @step_name         = N'02 - Segunda consulta',
   @step_id           = 2,
   @subsystem         = N'TSQL',
   @database_name     = N'DEV',
   @command           = N'
SELECT
   N''STEP 2'' AS StepExecutado,
   COUNT_BIG(*) AS ObjetosVisiveis
FROM sys.objects;',
   @on_success_action = 1,
   @on_fail_action    = 2;
GO

-->VALIDAR:
SELECT step_id, step_name, on_success_action, on_fail_action
FROM msdb.dbo.sysjobsteps
WHERE job_id = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name=N'DEV_JOB')
ORDER BY step_id;
GO

10. REMOVER O SEGUNDO STEP

EXEC msdb.dbo.sp_delete_jobstep
    @job_name = N'DEV_JOB',
    @step_id  = 2;
GO

-->Volte o step 1 para terminar o Job:
EXEC msdb.dbo.sp_update_jobstep
    @job_name          = N'DEV_JOB',
    @step_id           = 1,
    @on_success_action = 1;
GO

11. ADICIONAR UMA SEGUNDA AGENDA

EXEC msdb.dbo.sp_add_jobschedule
   @job_name               = N'DEV_JOB',
   @name                   = N'LAB_R40_SEMANAL',
   @enabled                = 0,
   @freq_type              = 8,       -- semanal
   @freq_interval          = 2,       -- segunda-feira
   @freq_recurrence_factor = 1,       -- a cada 1 semana
   @freq_subday_type       = 1,       -- uma vez no dia
   @active_start_time      = 040000;  -- 04:00:00
GO

12. REMOVER A SEGUNDA AGENDA

-->Desanexar:
EXEC msdb.dbo.sp_detach_schedule
   @job_name      = N'DEV_JOB',
   @schedule_name = N'LAB_R40_SEMANAL';
GO

-->Excluir a agenda órfã:
IF EXISTS (SELECT 1 FROM msdb.dbo.sysschedules WHERE name=N'LAB_R40_SEMANAL')
BEGIN
   EXEC msdb.dbo.sp_delete_schedule
       @schedule_name = N'LAB_R40_SEMANAL',
       @force_delete  = 0;
END;
GO

13. TROCAR OWNER — OPCIONAL

--> O Job criado pelo tenant já nasce com o owner do login criador. Para transferir para OUTRO LOGIN DO MESMO TENANT, use:
EXEC msdb.dbo.usp_TenantSqlAgentJob_SetOwner
   @JobName       = N'DEV_JOB',
   @NewOwnerLogin = N'<OUTRO_LOGIN_DO_MESMO_TENANT>';
GO

14. EXCLUIR TUDO

--> Executar logado com o login aplicado no passo 13 anteriormente
EXEC msdb.dbo.sp_delete_job
   @job_name = N'DEV_JOB',
   @delete_unused_schedule = 1;
GO

-->VALIDAÇÃO FINAL:
SELECT * FROM msdb.dbo.sysjobs
WHERE name = N'DEV_JOB';

SELECT * FROM msdb.dbo.sysschedules
WHERE name IN (N'LAB_R40_DIARIO', N'LAB_R40_SEMANAL');
GO