关闭

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

标签: sqlserver数据库存储databaseoutputaction
757人阅读 评论(0) 收藏 举报

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
 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:2422次
    • 积分:38
    • 等级:
    • 排名:千里之外
    • 原创:2篇
    • 转载:1篇
    • 译文:0篇
    • 评论:0条
    文章分类
    文章存档