IF NOT EXISTS(SELECT b.step_name FROM [msdb].[dbo].[sysjobs] a WITH(NOLOCK)

            INNER JOIN [msdb].[dbo].[sysjobsteps] b WITH(NOLOCK) ON a.job_id = b.job_id

            WHERE a.Name = 'CleanWSCold' and b.step_name = 'CleanWSCold_StepOne')

BEGIN

--1.   Criar o Job

DECLARE @jobId BINARY(16)

EXEC  msdb.dbo.sp_add_job @job_name=N'CleanWSCold',

             @enabled=1,

             @notify_level_eventlog=2,

             @notify_level_email=2,

             @notify_level_netsend=2,

             @notify_level_page=2,

             @delete_level=0,

             @description=N'Job used to execute cleanup script tables from the query web service  the Cold.',

             @category_name=N'Database Maintenance',

             @owner_login_name=N'parameter0', @job_id = @jobId OUTPUT

select @jobId

EXEC msdb.dbo.sp_add_jobserver @job_name=N'CleanWSCold', @server_name = N'parameter1'

--2.   Criar o Step

EXEC msdb.dbo.sp_add_jobstep @job_name=N'CleanWSCold', @step_name=N'CleanWSCold_StepOne',

             @step_id=1,

             @cmdexec_success_code=0,

             @on_success_action=1,

             @on_fail_action=2,

             @retry_attempts=0,

             @retry_interval=0,

             @os_run_priority=0, @subsystem=N'TSQL',

             @command='-- ****************** Area de declaração das variáveis e objetos temporários ****************************

-- Qtde de dias de retenção por exemplo 02 dias. Todos os dados que tiverem data superior a 02 dias corridos serao apagados se os flag de situação forem positivos.

declare @Period smallint,

       @DateRetention datetime

-- Definir valores de variáveis e sets

set @Period = ''2'' -- dias

set @DateRetention = GETDATE() - @Period

set nocount on

set xact_abort on

-- ****************** INICIO REGRAS DE LIMPEZA **************************** --

Begin TRY

       Begin TRAN

       -- delete de registros da tbColdSearchResult qdo o processamento ja foi concluido apos 48 hs (@Periodo)

Delete tbColdSearchResult where ColdSearchId in (Select ColdSearchId from tbColdSearch where LastProcessDate <= @DateRetention  and SearchStatus = 2)

              update tbColdSearch set SearchStatus = 3 where LastProcessDate <= @DateRetention and SearchStatus = 2

       Commit TRAN

       set xact_abort off

End TRY

Begin CATCH

       Rollback TRAN

       set xact_abort off

End CATCH

',

             @database_name=N'parameter2',

             @flags=4

--3.   Criar a notificação

EXEC msdb.dbo.sp_update_job @job_name=N'CleanWSCold',

             @enabled=1,

             @start_step_id=1,

             @notify_level_eventlog=2,

             @notify_level_email=2,

             @notify_level_netsend=2,

             @notify_level_page=2,

             @delete_level=0,

             @description=N'Job used to execute cleanup script tables from the query web service  the Cold.

',

             @category_name=N'Database Maintenance',

             @owner_login_name=N'parameter0',

             @notify_email_operator_name=N'',

             @notify_netsend_operator_name=N'',

             @notify_page_operator_name=N''

--4.   Criar o Schedule

DECLARE @schedule_id int

EXEC msdb.dbo.sp_add_jobschedule @job_name=N'CleanWSCold', @name=N'CleanWSCold_Schedule',

             @enabled=1,

             @freq_type=4,

             @freq_interval=1,

             @freq_subday_type=1,

             @freq_subday_interval=0,

             @freq_relative_interval=0,

             @freq_recurrence_factor=1,

             @active_start_date=parameter3,

             @active_end_date=99991231,

             @active_start_time=parameter4,

             @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

select @schedule_id

END