Use SQL jobs in Asp.net to Implement Asynchronous calling of stored procedures.

Because of Asp.net's BS mechanism, we have been executing a complex, ultra-long stored procedure for a long time, the page times out and stops responding. This method can be implemented through SQL jobs.ProgramTemporary Job for asynchronous call

/*************************************** ***************************************
* Create a temporary job in SQL Server 2000 for executing asynchronous calls.
* @ Execsql: Transact-SQL batch
* Eample: exec DBO. asynchronousinvoking @ execsql = 'updtae customer set balance = 0'
* Disadvantage: the database name must be specified for this stored procedure.
**************************************** **************************************/
Create procedure Bak
@ Execsql nvarchar (4000 ),
@ Job nvarchar (50 ),
@ Servern nvarchar (50 ),
@ Dbname nvarchar (50)
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 = @ job,
@ Owner_login_name = n '',
@ Description = n 'description for job', -- the description of the job
@ Category_name = n' [uncategorized (local)] ',
@ Enabled = 1,
@ Policy_level_email = 0,
@ Policy_level_page = 0,
@ Policy_level_netsend = 0,
@ Policy_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 database name must be specified for the stored procedure.
@ Database_name = @ dbname, -- the Database Name of the job to manipulate
@ Server = n '',
@ Database_user_name = n '',
@ 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 = @ servern -- Name of the input server

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