應(yīng)用場景：
在龐大的數(shù)據(jù)庫中很多復(fù)雜的更新查詢非常的耗時。為了避免用戶長時間的等待，那些耗時的操作可以采用異步執(zhí)行的方法，立刻返回執(zhí)行信息給用戶，同時在數(shù)據(jù)庫后臺執(zhí)行操作，等到執(zhí)行完畢再更新數(shù)據(jù)表。
開發(fā)環(huán)境：
sql server2000 .net
解決方案：
在sql server2000中創(chuàng)建一個臨時的job，（或者固定的job，根據(jù)具體的應(yīng)用場景），傳遞需要執(zhí)行的sql batch腳本，然后啟動這個job。這樣就可以在數(shù)據(jù)庫獲得異步調(diào)用的功能了。由于創(chuàng)建的是臨時的job，
sql server在該job運(yùn)行結(jié)束以后會自動刪除該job。
缺點(diǎn)：該存儲過程必須指定數(shù)據(jù)庫的名字
====================================================================================
/******************************************************************************
* author: iret
* desc: create temporary job to provide asynchronously invoking sql batch
* 在sql server 2000中創(chuàng)建用于執(zhí)行異步調(diào)用的臨時job
* @execsql: transact-sql batch
* eample: exec dbo.asynchronousinvoking @execsql = 'updtae customer set balance = 0'
* 缺點(diǎn)：該存儲過程必須指定數(shù)據(jù)庫的名字
* modified date: 2004/11/03
******************************************************************************/
create procedure dbo.asynchronousinvoking
@execsql nvarchar(4000)
as
begin transaction
declare @jobid binary(16)
declare @returncode int
select @returncode = 0
begin
-- add the job
execute @returncode = msdb.dbo.sp_add_job @job_id = @jobid output ,
@job_name = n'temp_sqljob',
@owner_login_name = n'',
@description = n'description for job', -- the description of the job
@category_name = n'[uncategorized (local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 0,
@delete_level= 3
if (@@error <> 0 or @returncode <> 0) goto quitwithrollback
-- add the job steps
execute @returncode = msdb.dbo.sp_add_jobstep @job_id = @jobid,
@step_id = 1,
@step_name = n'step1',
@command = @execsql, -- sql batch
--缺點(diǎn)：該存儲過程必須指定數(shù)據(jù)庫的名字
@database_name = n'your_database_name', --the database name of the job to manipulate
@server = n'',
@database_user_name = n'appuser',
@subsystem = n'tsql',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0, --execute once only
@retry_interval = 0,
@output_file_name = n'',
@on_success_step_id = 0,
@on_success_action = 1, -- on success abort
@on_fail_step_id = 0,
@on_fail_action = 2 -- on fail abort
if (@@error <> 0 or @returncode <> 0) goto quitwithrollback
--set the star step id of the job
execute @returncode = msdb.dbo.sp_update_job @job_id = @jobid,
@start_step_id = 1
if (@@error <> 0 or @returncode <> 0) goto quitwithrollback
-- add the target servers
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) begin
rollback transaction
return 1
end
endsave:
--star the job immediately
exec @returncode = msdb.dbo.sp_start_job @job_id = @jobid
--return to the invoker immediately
return @returncode
go
set quoted_identifier off
go
set ansi_nulls on
go
，歡迎訪問網(wǎng)頁設(shè)計愛好者web開發(fā)。
