To create a temporary job by using SQL Server 2000 to execute the SQL script asynchronously

server| Create | scripts | asynchronous | execute
Application Scenario:
Many complex update queries are time-consuming in a large database. In order to avoid the user waiting for a long time, those time-consuming operations can be performed asynchronously, immediately return the execution information to the user, while performing operations in the database background, wait until the completion of the data table update.

Development environment:
SQL SERVER2000. NET

Solution:
Create a temporary job in SQL SERVER2000 (or a fixed job, based on a specific scenario), pass the SQL batch script that needs to be executed, and then start the job. This allows you to get the functionality of the asynchronous invocation in the database. Because a temporary job was created,
SQL Server automatically deletes the job after the job has finished running.

Disadvantage: The stored procedure must specify the name of the database

====================================================================================
/******************************************************************************
* Author:iret
* Desc:create temporary job to provide asynchronously invoking SQL batch
* Create a temporary job in SQL SERVER 2000 to perform an asynchronous call
* @EXECSQL: Transact-SQL batch
* EAMPLE:EXEC dbo. asynchronousinvoking @EXECSQL = ' updtae customer SET balance = 0 '
* Disadvantage: The stored procedure must specify the name of the database
* 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
--Disadvantage: The stored procedure must specify the name of the database
@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