使用SQLServer2000創(chuàng)建臨時(shí)Job異步執(zhí)行SQL腳

應(yīng)用場景：
     在龐大的數(shù)據(jù)庫中很多復(fù)雜的更新查詢非常的耗時(shí)。為了避免用戶長時(shí)間的等待，那些耗時(shí)的操作可以采用異步執(zhí)行的方法，立刻返回執(zhí)行信息給用戶，同時(shí)在數(shù)據(jù)庫后臺(tái)執(zhí)行操作，等到執(zhí)行完畢再更新數(shù)據(jù)表。
    開發(fā)環(huán)境：
    SQL SERVER2000 .NET
    解決方案：
     在SQL SERVER2000中創(chuàng)建一個(gè)臨時(shí)的Job，（或者固定的Job，根據(jù)具體的應(yīng)用場景），傳遞需要執(zhí)行的SQL batch腳本，然后啟動(dòng)這個(gè)Job。這樣就可以在數(shù)據(jù)庫獲得異步調(diào)用的功能了。由于創(chuàng)建的是臨時(shí)的Job，
    SQL SERVER在該Job運(yùn)行結(jié)束以后會(huì)自動(dòng)刪除該Job。
    缺點(diǎn)：該存儲(chǔ)過程必須指定數(shù)據(jù)庫的名字
    ====================================================================================
    /******************************************************************************
     * Author: iret
     * Desc: Create temporary job to provide asynchronously invoking SQL batch
     * 在SQL SERVER 2000中創(chuàng)建用于執(zhí)行異步調(diào)用的臨時(shí)Job
     * @EXECSQL: Transact-SQL batch
     * Eample: EXEC dbo.AsynchronousInvoking @EXECSQL = ’UPDTAE customer SET balance = 0’
     * 缺點(diǎn)：該存儲(chǔ)過程必須指定數(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)：該存儲(chǔ)過程必須指定數(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