Выполнение периодических задач в 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 (
)
)
Link to this page:
//www.vb-net.com/PeriodicAspNetJob/CreateDynamicJob.htm
|
|