应用场景：
在庞大的数据库中很多复杂的更新查询非常的耗时。为了避免用户长时间的等待，那些耗时的操作可以采用异步执行的方法，立刻返回执行信息给用户，同时在数据库后台执行操作，等到执行完毕再更新数据表。
开发环境：
sql server2000 .net
解决方案：
在sql server2000中创建一个临时的job，（或者固定的job，根据具体的应用场景），传递需要执行的sql batch脚本，然后启动这个job。这样就可以在数据库获得异步调用的功能了。由于创建的是临时的job，
sql server在该job运行结束以后会自动删除该job。
缺点：该存储过程必须指定数据库的名字
====================================================================================
/******************************************************************************
* author: iret
* desc: create temporary job to provide asynchronously invoking sql batch
* 在sql server 2000中创建用于执行异步调用的临时job
* @execsql: transact-sql batch
* eample: exec dbo.asynchronousinvoking @execsql = 'updtae customer set balance = 0'
* 缺点：该存储过程必须指定数据库的名字
* 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
--缺点：该存储过程必须指定数据库的名字
@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
，欢迎访问网页设计爱好者web开发。
