Выполнение периодических задач в ASP.NET

Задания я создаю вот такой своей процедурой:


   1:  ALTER        procedure [dbo].[CreateScheduleJob]
   2:  -- ВЫПОЛНЯЕМАЯ SQL_АГЕНТОМ SQL_ПРОЦЕДУРА ИЛИ SQL_СБОРКА       
   3:  @SQL_command as nvarchar(1000) = '', 
   4:  @schedule_start_date as int = 20060101,     -- соответствуют параметрам sp_add_jobschedule
   5:  @schedule_end_date as int   = 99991231,     -- соответствуют параметрам sp_add_jobschedule
   6:  @schedule_start_time as int = 0,            -- соответствуют параметрам sp_add_jobschedule
   7:  @schedule_end_time as int   = 235959,        -- соответствуют параметрам sp_add_jobschedule
   8:  @schedule_freq_type as int =4,                -- соответствуют параметрам sp_add_jobschedule
   9:  @schedule_freq_interval  as int= 1,         -- соответствуют параметрам sp_add_jobschedule
  10:  @schedule_freq_subday_type  as int= 4,         -- соответствуют параметрам sp_add_jobschedule
  11:  @schedule_freq_subday_interval as int = 1,  -- соответствуют параметрам sp_add_jobschedule
  12:  @schedule_freq_relative_interval as int = 0,-- соответствуют параметрам sp_add_jobschedule
  13:  @schedule_freq_recurrence_factor as int = 0,-- соответствуют параметрам sp_add_jobschedule
  14:  @job_comment as nvarchar(1000) = '',
  15:  @FullReport as bit=0
  16:  as
  17:  declare @job_step_name nvarchar(100), @Job_Name as nvarchar(1100), @jobschedule_name nvarchar(100),@dbName sysname
  18:  select  @job_name=db_name()+ '_TimerTask_'+ @job_comment + ' ' + cast(newid() as nvarchar(100)), @job_step_name =cast(newid() as nvarchar(100)), @jobschedule_name =  cast(newid() as nvarchar(100)),@dbName=db_name()
  19:  DECLARE @JobID BINARY(16)
  20:  --DECLARE @ReturnCode INT    
  21:  --SELECT  @ReturnCode = 0  
  22:  DECLARE @ReturnCode INT ,  @C_User sysname   
  23:  SELECT  @ReturnCode = 0 ,  @C_User=SUser_Sname()
  24:  BEGIN TRANSACTION  
  25:  IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'TimerTask') < 1 
  26:    -- Add the category
  27:    EXECUTE msdb.dbo.sp_add_category @name = N'TimerTask'
  28:    If @FullReport=1 EXECUTE msdb.dbo.sp_help_category
  29:    SELECT @JobID = job_id  FROM   msdb.dbo.sysjobs  WHERE (name = @job_name)       
  30:    IF (@JobID IS NULL)    
  31:    BEGIN  
  32:       select  @job_name    as [JOB_NAME]
  33:       -- Add the job
  34:       EXECUTE @ReturnCode = msdb.dbo.sp_add_job  @job_name , @owner_login_name = @C_User, @description=@job_comment , @category_name = N'TimerTask', @enabled = 0, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0, @job_id = @JobID OUTPUT 
  35:       IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  36:       If @FullReport=1 EXECUTE msdb.dbo.sp_help_job
  37:       -- Add the job steps
  38:       EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = @job_step_name , @command = @SQL_command, @database_name = @dbName, @server = N'', @database_user_name = N'dbo', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  39:       IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  40:       EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 
  41:       IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  42:       If @FullReport=1 EXECUTE msdb.dbo.sp_help_jobstep @job_id = @JobID
  43:       -- Add the job schedules
  44:       EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = @jobschedule_name , @enabled = 1, @freq_type = @schedule_freq_type, @active_start_date = @schedule_start_date, @active_start_time = @schedule_start_time, @freq_interval = @schedule_freq_interval , @freq_subday_type = @schedule_freq_subday_type, @freq_subday_interval = @schedule_freq_subday_interval, @freq_relative_interval = @schedule_freq_relative_interval, @freq_recurrence_factor = @schedule_freq_recurrence_factor, @active_end_date = @schedule_end_date, @active_end_time = @schedule_end_time
  45:       IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  46:       If @FullReport=1 EXECUTE msdb.dbo.sp_help_jobschedule @job_id = @JobID
  47:       -- Add the Target Servers
  48:       EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  49:       IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  50:       If @FullReport=1 EXECUTE msdb.dbo.sp_help_jobserver @job_id = @JobID
  51:       -- 
  52:       EXECUTE msdb.dbo.sp_update_job @job_id = @JobID , @enabled = 1 
  53:    END
  54:  ELSE GOTO QuitWithRollback 
  55:  COMMIT TRANSACTION          
  56:  GOTO   EndSave              
  57:  QuitWithRollback:
  58:    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
  59:  EndSave:

В базе сайта я храню первичную таблицу - оригинал всех параметров созданных SQL-заданий. В этой таблице я храню и постоянные задания и динамически созданные пользователями и администрацией. Эта таблица находится в базе, которая постоянно бекапируется - таким образом ни одно задание никогда не потеряется.


   1:  CREATE TABLE [dbo].[SqlJob](
   2:      [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
   3:      [jobID] [uniqueidentifier] NULL,
   4:      [IsBlock] [bit] NULL,
   5:      [BackUp_job_name] [nvarchar](1100) NULL,
   6:      [BackUp_jobschedule_name] [nvarchar](100) NULL,
   7:      [BackUp_job_step_name] [nvarchar](100) NULL,
   8:      [BackUp_job_enable] [tinyint] NULL,
   9:      [BackUp_SQL_command] [varchar](1000) NULL,
  10:      [BackUp_schedule_start_date] [int] NULL,
  11:      [BackUp_schedule_end_date] [int] NULL,
  12:      [BackUp_schedule_start_time] [int] NULL,
  13:      [BackUp_schedule_end_time] [int] NULL,
  14:      [BackUp_schedule_freq_type] [int] NULL,
  15:      [BackUp_schedule_freq_interval] [int] NULL,
  16:      [BackUp_schedule_freq_subday_type] [int] NULL,
  17:      [BackUp_schedule_freq_subday_interval] [int] NULL,
  18:      [BackUp_schedule_freq_relative_interval] [int] NULL,
  19:      [BackUp_schedule_freq_recurrence_factor] [int] NULL,
  20:      [BackUp_job_comment] [nvarchar](1000) NULL,
  21:   
  22:   CONSTRAINT [PK_SY_MG_Reminders] PRIMARY KEY CLUSTERED 
  23:  (
  24:      [Id] ASC
  25:  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  26:  ) ON [PRIMARY]
  27:   
  28:  GO

После восстановления базы с некоторой контрольной точки я восстанавливаю из бекапа все задания и создаю их в MSDB.


   1:  ALTER              procedure [dbo].[SY_MG_RestoreAllScheduleJob]
   2:  --восставление заданий в MSDB после архивирования
   3:  as
   4:  Declare Reminders CURSOR FOR SELECT 
   5:  [Id],
   6:  jobID,
   7:  BackUp_job_name,
   8:  BackUp_jobschedule_name,
   9:  BackUp_job_step_name,
  10:  BackUp_SQL_command,
  11:  BackUp_schedule_start_date,
  12:  BackUp_schedule_end_date,
  13:  BackUp_schedule_start_time,
  14:  BackUp_schedule_end_time,
  15:  BackUp_schedule_freq_type,
  16:  BackUp_schedule_freq_interval,
  17:  BackUp_schedule_freq_subday_type,
  18:  BackUp_schedule_freq_subday_interval,
  19:  BackUp_schedule_freq_relative_interval,
  20:  BackUp_schedule_freq_recurrence_factor,
  21:  BackUp_job_comment,
  22:  BackUp_job_enable
  23:  FROM SqlJob
  24:  declare @Id int,
  25:      @jobID uniqueidentifier,
  26:      @BackUp_job_name nvarchar (1100),
  27:      @BackUp_jobschedule_name nvarchar (100),
  28:      @BackUp_job_step_name nvarchar (100),
  29:      @BackUp_SQL_command varchar (1000),
  30:      @BackUp_schedule_start_date int,
  31:      @BackUp_schedule_end_date int ,
  32:      @BackUp_schedule_start_time int ,
  33:      @BackUp_schedule_end_time int ,
  34:      @BackUp_schedule_freq_type int ,
  35:      @BackUp_schedule_freq_interval int ,
  36:      @BackUp_schedule_freq_subday_type int ,
  37:      @BackUp_schedule_freq_subday_interval int ,
  38:      @BackUp_schedule_freq_relative_interval int ,
  39:      @BackUp_schedule_freq_recurrence_factor int ,
  40:      @BackUp_job_comment nvarchar (1000),
  41:      @BackUp_job_enable tinyint
  42:  Declare @NewJodId uniqueidentifier,@dbName as sysname
  43:  DECLARE @ReturnCode INT ,  @C_User sysname   
  44:  SELECT  @ReturnCode = 0 ,  @C_User=SUser_Sname()
  45:  --DECLARE @ReturnCode INT    
  46:  --SELECT  @ReturnCode = 0  
  47:  IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'FromSVA') < 1 
  48:    EXECUTE msdb.dbo.sp_add_category @name = N'FromSVA'
  49:  OPEN Reminders
  50:  FETCH NEXT FROM Reminders INTO 
  51:  @Id,
  52:  @jobID,
  53:  @BackUp_job_name,
  54:  @BackUp_jobschedule_name,
  55:  @BackUp_job_step_name,
  56:  @BackUp_SQL_command,
  57:  @BackUp_schedule_start_date,
  58:  @BackUp_schedule_end_date,
  59:  @BackUp_schedule_start_time,
  60:  @BackUp_schedule_end_time,
  61:  @BackUp_schedule_freq_type,
  62:  @BackUp_schedule_freq_interval,
  63:  @BackUp_schedule_freq_subday_type,
  64:  @BackUp_schedule_freq_subday_interval,
  65:  @BackUp_schedule_freq_relative_interval,
  66:  @BackUp_schedule_freq_recurrence_factor,
  67:  @BackUp_job_comment,
  68:  @BackUp_job_enable
  69:  WHILE @@FETCH_STATUS = 0
  70:  BEGIN
  71:  select @BackUp_job_name as '@BackUp_job_name'
  72:       SELECT @NewJodId=NULL,@dbName=db_name()
  73:       SELECT @NewJodId = job_id  FROM  msdb.dbo.sysjobs  WHERE (name = @BackUp_job_name)       
  74:  select @NewJodId as '@NewJodId'
  75:       IF (@NewJodId IS NULL)  BEGIN  
  76:  select @@TRANCOUNT,'IDLE'    
  77:      
  78:           BEGIN TRANSACTION
  79:  select @@TRANCOUNT,'START'    
  80:               select  @BackUp_job_name    as [JOB_NAME]
  81:           
  82:               EXECUTE @ReturnCode = msdb.dbo.sp_add_job  
  83:                       @job_name =@BackUp_job_name , 
  84:                       @owner_login_name = @C_User, 
  85:                       @description= @Id ,  
  86:                       @category_name = N'FromSVA', 
  87:                       @enabled = 0, 
  88:                       @notify_level_email = 0, 
  89:                       @notify_level_page = 0, 
  90:                       @notify_level_netsend = 0, 
  91:                       @notify_level_eventlog = 2, 
  92:                       @delete_level= 0, 
  93:                       @job_id = @NewJodId OUTPUT 
  94:           IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  95:  select @@TRANCOUNT,'sp_add_job'
  96:                 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
  97:                       @job_id = @NewJodId, 
  98:               @step_id = 1, 
  99:               @step_name = @BackUp_job_step_name, 
 100:               @command = @BackUp_SQL_command, 
 101:               @database_name = @dbName, 
 102:               @server = N'', 
 103:               @database_user_name = N'dbo', 
 104:               @subsystem = N'TSQL', 
 105:               @cmdexec_success_code = 0, 
 106:               @flags = 0, 
 107:               @retry_attempts = 0, 
 108:               @retry_interval = 1, 
 109:               @output_file_name = N'', 
 110:               @on_success_step_id = 0, 
 111:               @on_success_action = 1, 
 112:               @on_fail_step_id = 0, 
 113:               @on_fail_action = 2
 114:                  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
 115:  select @@TRANCOUNT,'sp_add_jobstep'
 116:                EXECUTE @ReturnCode = msdb.dbo.sp_update_job  
 117:               @job_id = @NewJodId, 
 118:               @start_step_id = 1 
 119:                IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
 120:  select @@TRANCOUNT,'sp_update_job'
 121:                EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule 
 122:               @job_id = @NewJodId, 
 123:                @name = @BackUp_jobschedule_name , 
 124:               @enabled = 1, 
 125:               @freq_type = @BackUp_schedule_freq_type, 
 126:               @active_start_date = @BackUp_schedule_start_date, 
 127:               @active_start_time = @BackUp_schedule_start_time, 
 128:               @freq_interval = @BackUp_schedule_freq_interval , 
 129:               @freq_subday_type = @BackUp_schedule_freq_subday_type, 
 130:               @freq_subday_interval = @BackUp_schedule_freq_subday_interval, 
 131:               @freq_relative_interval = @BackUp_schedule_freq_relative_interval, 
 132:               @freq_recurrence_factor = @BackUp_schedule_freq_recurrence_factor, 
 133:               @active_end_date = @BackUp_schedule_end_date, 
 134:               @active_end_time = @BackUp_schedule_end_time
 135:                IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
 136:  select @@TRANCOUNT,'sp_add_jobschedule'
 137:                EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver 
 138:               @job_id = @NewJodId, 
 139:               @server_name = N'(local)' 
 140:                IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
 141:  select @@TRANCOUNT,'sp_add_jobserver'
 142:                EXECUTE msdb.dbo.sp_update_job  
 143:               @job_id = @NewJodId , 
 144:               @enabled = @BackUp_job_enable
 145:           IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
 146:  select @@TRANCOUNT,'sp_update_job'
 147:           --сделали задание - теперь нада поставить связку
 148:   
 149:               UPDATE SqlJob SET [jobID]=@NewJodId 
 150:               WHERE CURRENT OF Reminders
 151:           IF (@@RowCount<>1) GOTO QuitWithRollback 
 152:  select @@TRANCOUNT,'Update'
 153:                COMMIT TRANSACTION          
 154:               print 'COMMIT'
 155:       END
 156:       ELSE --такое задание уже существует - не меняем его
 157:            GOTO NextJob
 158:             
 159:       QuitWithRollback:
 160:  select @@TRANCOUNT,'DBCC'
 161:       dbcc opentran    --отладка
 162:       IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
 163:       NextJob:
 164:       FETCH NEXT FROM Reminders INTO 
 165:       @Id,
 166:       @jobID,
 167:       @BackUp_job_name,
 168:       @BackUp_jobschedule_name,
 169:       @BackUp_job_step_name,
 170:       @BackUp_SQL_command,
 171:       @BackUp_schedule_start_date,
 172:       @BackUp_schedule_end_date,
 173:       @BackUp_schedule_start_time,
 174:       @BackUp_schedule_end_time,
 175:       @BackUp_schedule_freq_type,
 176:       @BackUp_schedule_freq_interval,
 177:       @BackUp_schedule_freq_subday_type,
 178:       @BackUp_schedule_freq_subday_interval,
 179:       @BackUp_schedule_freq_relative_interval,
 180:       @BackUp_schedule_freq_recurrence_factor,
 181:       @BackUp_job_comment,
 182:       @BackUp_job_enable
 183:  END
 184:  CLOSE Reminders
 185:  DEALLOCATE Reminders
 186:   
 187:   


Comments ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19>  <20>  <21>  <22>  <23
Link to this page: //www.vb-net.com/PeriodicAspNetJob/CreateDynamicJob.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>