利用作业调度生成事务预报 2007-04-12 baker
1. 问题的提出:
实验室项目需要做一个事务预报的模块, 利用该模块生成预报控制信息, 如果数据库存在即将验收的项目信息, 需要提示用户准备对该项目进行验收. 以及产生如下效果:
并生成显示预报信息页面, 用户还可根据需求对项目清单进行查询.
如果没有需要预报的项目就将显示以下窗口
1. 解决方案:
由于需要生成的预报信息的事件是不寻常的, 最简单的方法就是在每个用户登录时 ,判断是否存在需要预报的信息, 所有预报信息都是临时生成的, 如果主表信息教多, 对整个系统性能有较大影响, 并且多次处理具有相同功能的任务, 造成较大浪费.
在 sql server 中有一个 sql server agent服务, 该服务有一个”作业功能” , 很像是一个定时触发器, 利用”作业” 在特定时间执行特定的t-sql语句或过程是一个效率, 效果并存的方法.
2. 生成预报控制表.
/*
创建表pybkz
默认插入一条固定记录('0000000000000000','0','0',null).
*/
use kjxm
go
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[pybkz]') and objectproperty(id, n'istable') = 1)
drop table pybkz
go
create table pybkz
(
登录名varchar(16) not null,
验收预报varchar(1) default(0),
拨款预报varchar(1) default(0),
预报年月varchar(6) default(0)
)
insert into pybkz select '0000000000000000','0','0',null
go
--设置sqlserveragent 为自启动
exec msdb.dbo.sp_set_sqlagent_properties @auto_start=1
go
3. 创建作业
--—2007-04-11/21:47
--— by baker
--—服务器: (local)
begin transaction
declare @jobid binary(16)
declare @returncode int
select @returncode = 0
if (select count(*) from msdb.dbo.syscategories where name = n'[uncategorized (local)]') < 1
execute msdb.dbo.sp_add_category @name = n'[uncategorized (local)]'
-- 删除同名的警报（如果有的话）。
select @jobid = job_id
from msdb.dbo.sysjobs
where (name = n'kjxm_forecast')
if (@jobid is not null)
begin
-- 检查此作业是否为多重服务器作业
if (exists (select *
from msdb.dbo.sysjobservers
where (job_id = @jobid) and (server_id <> 0)))
begin
-- 已经存在，因而终止脚本
raiserror (n'无法导入作业“kjxm_forecast”，因为已经有相同名称的多重服务器作业。', 16, 1)
goto quitwithrollback
end
else
-- 删除［本地］作业
execute msdb.dbo.sp_delete_job @job_name = n'kjxm_forecast'
select @jobid = null
end
begin
-- 添加作业
execute @returncode = msdb.dbo.sp_add_job @job_id = @jobid output , @job_name = n'kjxm_forecast', @owner_login_name = n'sa', @description = n'没有可用的描述。', @category_name = n'[uncategorized (local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
if (@@error <> 0 or @returncode <> 0) goto quitwithrollback
-- 添加作业步骤
execute @returncode = msdb.dbo.sp_add_jobstep @job_id = @jobid, @step_id = 1, @step_name = n'1', @command = n'use kjxm go exec forecast', @database_name = n'kjxm', @server = n'', @database_user_name = n'', @subsystem = n'tsql', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 5, @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
if (@@error <> 0 or @returncode <> 0) goto quitwithrollback
execute @returncode = msdb.dbo.sp_update_job @job_id = @jobid, @start_step_id = 1
if (@@error <> 0 or @returncode <> 0) goto quitwithrollback
-- 添加作业调度
execute @returncode = msdb.dbo.sp_add_jobschedule @job_id = @jobid, @name = n'2', @enabled = 1, @freq_type = 4, @active_start_date = 20070411, @active_start_time = 100, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
if (@@error <> 0 or @returncode <> 0) goto quitwithrollback
-- 添加目标服务器
execute @returncode = msdb.dbo.sp_add_jobserver @job_id = @jobid, @server_name = n'(local)'
if (@@error <> 0 or @returncode <> 0) goto quitwithrollback
end
commit transaction
goto endsave
quitwithrollback:
if (@@trancount > 0) rollback transaction
endsave:
4. 生成 预报信息存储过程.
/*
创建用于生成预报信息的存储过程
*/
use kjxm
go
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[forecast]') and objectproperty(id, n'isprocedure') = 1)
drop procedure [dbo].[forecast]
go
create procedure forecast
as
begin transaction
begin
declare @have_forecast int
declare @time datetime
declare @nextmonth varchar(6)
--生成下月日期字符串, 例如:20070501
set @time= dateadd(mm,datediff(mm,0,getdate())+1,0)
if(month(@time)<10)
begin
set @nextmonth=convert(varchar(4),year(@time))+'0'+convert(varchar(2),month(@time))
end
else
begin
set @nextmonth=convert(varchar(4),year(@time))+convert(varchar(2),month(@time))
end
--判断当前日期是否为该月第一天,如果为第一天则更新pybkz表中预报记录.
if((not exists(select 预报年月from pybkz where 登录名='0000000000000000')) or (select 预报年月from pybkz where 登录名='0000000000000000')[email protected] )
begin
delete from pybkz
set @have_forecast=0
if ((select count(*) from pxmxx where 完成日期[email protected])>0)
begin
insert into pybkz select '0000000000000000','1','1',@nextmonth
end
else
begin
insert into pybkz select '0000000000000000','0','0',@nextmonth
end
end
end
commit transaction
go
5. 读取预报信息.
/*
判断某一个登录名是否需要提示预报信息, 并设置相应的预报信息.
*/
use kjxm
go
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[is_forecast]') and objectproperty(id, n'isprocedure') = 1)
drop procedure [dbo].[is_forecast]
go
create procedure is_forecast
(
@user varchar(16)
)
as
declare @nextmonth varchar(6)
set @nextmonth=(select 预报年月from pybkz where 登录名='0000000000000000')
if(exists(select * from pybkz where 登录名='0000000000000000' and 验收预报='1'))
begin
if(exists(select * from pybkz where 登录名[email protected] and 验收预报='0'))
begin
select 0
end
if(exists(select * from pybkz where 登录名[email protected] and 验收预报='1'))
begin
select 1
end
if(not exists(select * from pybkz where 登录名[email protected]))
begin
insert into pybkz select @user,'1','1',@nextmonth
select 1
end
end
else
begin
select 0
end
