IDC资讯中心
应用场景：
在庞大的数据库中很多复杂的更新查询非常的耗时。为了避免用户长时间的等待，那些耗时的操作可以采用异步执行的方法，立刻返回执行信息给用户，同时在数据库后台执行操作，等到执行完毕再更新数据表。
开发环境：
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 = ntemp_sqljob,
@owner_login_name = n,
@description = ndescription 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 = nstep1,
@command = @execsql, — sql batch
–缺点：该存储过程必须指定数据库的名字
@database_name = nyour_database_name, –the database name of the job to manipulate
@server = n,
@database_user_name = nappuser,
@subsystem = ntsql,
@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
