利用作業(yè)調(diào)度生成事務(wù)預(yù)報 2007-04-12 baker
1. 問題的提出:
實驗室項目需要做一個事務(wù)預(yù)報的模塊, 利用該模塊生成預(yù)報控制信息, 如果數(shù)據(jù)庫存在即將驗收的項目信息, 需要提示用戶準(zhǔn)備對該項目進(jìn)行驗收. 以及產(chǎn)生如下效果:
并生成顯示預(yù)報信息頁面, 用戶還可根據(jù)需求對項目清單進(jìn)行查詢.
如果沒有需要預(yù)報的項目就將顯示以下窗口
1. 解決方案:
由于需要生成的預(yù)報信息的事件是不尋常的, 最簡單的方法就是在每個用戶登錄時 ,判斷是否存在需要預(yù)報的信息, 所有預(yù)報信息都是臨時生成的, 如果主表信息教多, 對整個系統(tǒng)性能有較大影響, 并且多次處理具有相同功能的任務(wù), 造成較大浪費(fèi).
在 sql server 中有一個 sql server agent服務(wù), 該服務(wù)有一個”作業(yè)功能” , 很像是一個定時觸發(fā)器, 利用”作業(yè)” 在特定時間執(zhí)行特定的t-sql語句或過程是一個效率, 效果并存的方法.
2. 生成預(yù)報控制表.
/*
創(chuàng)建表pybkz
默認(rèn)插入一條固定記錄('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,
驗收預(yù)報varchar(1) default(0),
撥款預(yù)報varchar(1) default(0),
預(yù)報年月varchar(6) default(0)
)
insert into pybkz select '0000000000000000','0','0',null
go
--設(shè)置sqlserveragent 為自啟動
exec msdb.dbo.sp_set_sqlagent_properties @auto_start=1
go
3. 創(chuàng)建作業(yè)
--—2007-04-11/21:47
--— by baker
--—服務(wù)器: (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
-- 檢查此作業(yè)是否為多重服務(wù)器作業(yè)
if (exists (select *
from msdb.dbo.sysjobservers
where (job_id = @jobid) and (server_id <> 0)))
begin
-- 已經(jīng)存在，因而終止腳本
raiserror (n'無法導(dǎo)入作業(yè)“kjxm_forecast”，因為已經(jīng)有相同名稱的多重服務(wù)器作業(yè)。', 16, 1)
goto quitwithrollback
end
else
-- 刪除［本地］作業(yè)
execute msdb.dbo.sp_delete_job @job_name = n'kjxm_forecast'
select @jobid = null
end
begin
-- 添加作業(yè)
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
-- 添加作業(yè)步驟
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
-- 添加作業(yè)調(diào)度
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
-- 添加目標(biāo)服務(wù)器
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. 生成 預(yù)報信息存儲過程.
/*
創(chuàng)建用于生成預(yù)報信息的存儲過程
*/
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
--判斷當(dāng)前日期是否為該月第一天,如果為第一天則更新pybkz表中預(yù)報記錄.
if((not exists(select 預(yù)報年月from pybkz where 登錄名='0000000000000000')) or (select 預(yù)報年月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. 讀取預(yù)報信息.
/*
判斷某一個登錄名是否需要提示預(yù)報信息, 并設(shè)置相應(yīng)的預(yù)報信息.
*/
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 預(yù)報年月from pybkz where 登錄名='0000000000000000')
if(exists(select * from pybkz where 登錄名='0000000000000000' and 驗收預(yù)報='1'))
begin
if(exists(select * from pybkz where 登錄名[email protected] and 驗收預(yù)報='0'))
begin
select 0
end
if(exists(select * from pybkz where 登錄名[email protected] and 驗收預(yù)報='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
