Application Scenario: Many complex update queries are very time consuming in a huge database. In order to avoid waiting for a long time, those time-consuming operations can use asynchronous execution, return to the execution information to the user, while executing operations in the database background, etc., etc., the data table is updated. Development Environment: SQL Server2000 .NET Solution: Create a temporary Job in SQL Server2000, (or fixed Job, according to specific application scenarios), pass the SQL BATCH script that needs to be executed, and then start this Job. This allows for the database to get asynchronous calls. Since it is a temporary Job, SQL Server will automatically delete the job after the Job run. Disadvantages: This stored process must specify the name of the database ======================================== ============================================ / ***** *********************************************************** *********************** * Author: IRET * DESC: CREATE TEMPORARY JOB TO PROVIDE Asynchronously Invoking SQL BATCH * Created in SQL Server 2000 to perform asynchronous Temporary Job * @execsql: Transact-SQL BATCH * Eample: Exec dbo.asynchronousInvoking @execsql = 'updtae customer set balance = 0' * Disadvantages: This 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 step = msdb.dbo.sp_add_jobstep @Job_ID = @Jobid, @step_id = 1, @step_name = n'step1 ', @Command = @EXECSQL, - SQL Batch - Disadvantages: This storage process 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 ENDCOMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@ TRANCOUNT> 0) BEGIN ROLLBACK TRANSACTION RETURN 1 ENDEndSave: --star the job immediatelyEXEC @ReturnCode = msdb.dbo.sp_start_job @ Job_id = @Jobid - Return to the invoker immediatelyreturn @returncodegoset quoted_identifier off goset Ansi_nulls on Go
