sqlserver数据库异步处理的存储过程

CREATE Procedure AsynchronousInvoking
 @EXECSQL nvarchar(4000)
AS

BEGIN TRANSACTION           
  DECLARE @JobID BINARY(16) 
  DECLARE @ReturnCode INT   
  SELECT @ReturnCode = 0    

BEGIN

  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',
     @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


  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
     @step_id = 1,
     @step_name = N'step1',
     @command = @EXECSQL,
     @database_name = N'master',
     @server = N'',
     @database_user_name = N'',
     @subsystem = N'TSQL',
     @cmdexec_success_code = 0,
     @flags = 0,
     @retry_attempts = 0,
     @retry_interval = 0,
     @output_file_name = N'',
     @on_success_step_id = 0,
     @on_success_action = 1,
     @on_fail_step_id = 0,
     @on_fail_action = 2
    
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID,
              @start_step_id = 1

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 

  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:

EXEC @ReturnCode = msdb.dbo.sp_start_job @job_id = @JobID
 
RETURN @ReturnCode


GO
 

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

sqlserver数据库异步处理的存储过程

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭