1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156
| USE [msdb]
GO
/****** Object: Job [CollecteDesIncoherences] Script Date: 06/12/2015 09:41:24 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 06/12/2015 09:41:25 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'CollecteDesIncoherences',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Pas de description disponible.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'rkenitra-maria\Mme Maria', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Remise à zero] Script Date: 06/12/2015 09:41:26 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Remise à zero',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'delete from Alertes
DBCC CHECKIDENT (''Alertes'', RESEED, 0)',
@database_name=N'DREKenitra_Services',
@output_file_name=N'D:\AgentSql.txt',
@flags=16
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [D_DA_Sup] Script Date: 06/12/2015 09:41:26 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'D_DA_Sup',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'insert into Alertes
SELECT ''D_DA_Sup'' as code, Guid, 0 as idRecette, Propriétaire,DF_aut, DATEDIFF(day,DF_aut ,GETDATE()) as difference ,
(''La date fin est expirée il y a ''+ convert(varchar(5),DATEDIFF(day,DF_aut ,GETDATE())) +'' jours''), getdate()
FROM SS
WHERE (Statut = ''Décision Autorisation validée'' or Statut = ''Attestation notification délivrée'')
AND DF_aut < GETDATE()
AND DATEDIFF(day ,DF_aut,GETDATE()) > 30',
@database_name=N'DREKenitra_Services',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [D_DF_Expiré] Script Date: 06/12/2015 09:41:26 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'D_DF_Expiré',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'insert into Alertes
SELECT ''D_DF_Expiré'' as code, Guid, 0 as idRecette, Propriétaire,DF_aut,
DATEDIFF(day,DF_aut ,GETDATE()) as difference ,
(''La date fin sera expirée dans ''+ convert(varchar(5),DATEDIFF(day,DF_aut ,GETDATE())) +'' jours''),
getdate()
FROM SS
WHERE (Statut = ''Attestation notification délivrée'' or Statut = ''Décision Autorisation validée'')
AND DF_aut < GETDATE()
AND DATEDIFF(day ,DF_aut,GETDATE()) <= 30',
@database_name=N'DREKenitra_Services',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Recettes_Update_Etat] Script Date: 06/12/2015 09:41:26 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Recettes_Update_Etat',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'Update Recettes set Recettes.Etat = ''Active''
WHERE (Recettes.Etat = ''Inactive'')
AND year(GETDATE()) = YEAR(Recettes.DateDebutRecette)
AND month(GETDATE()) =01
AND day(GETDATE()) = 10',
@database_name=N'DREKenitra_Services',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Recettes_EnAttentes] Script Date: 06/12/2015 09:41:26 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Recettes_EnAttentes',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'insert into Alertes
SELECT ''Recettes_EnAttentes'' AS code, SS.Guid, Recettes.id AS idRecette, SS.Propriétaire, SS.DF_aut, DATEDIFF(month ,Recettes.DateDebutRecette,GETDATE()) AS difference,
''La recette du ''+convert(varchar(20),Recettes.DateDebutRecette)+'' n''''est pas encore régularisée, ''+ convert(varchar(5),DATEDIFF(month ,Recettes.DateDebutRecette,GETDATE()))+'' mois de retard'' AS message,
getdate()
FROM SS INNER JOIN
Recettes ON SS.Guid = Recettes.Guid
WHERE (Recettes.Etat = ''Active'')
AND Recettes.StatutPaiement = ''Non Régularisé''',
@database_name=N'DREKenitra_Services',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'CollecteDesIncoherences',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20150609,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'a669940a-998a-4626-bead-9edeed6998e8'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO |
Partager