Create a database job

Use SQL statements to create steps, alarms, plans, notifications, and targets to execute a job. No USE [msdb] GO ****** Object: Job [DatabaWorkFlowSql] ScriptDate: 2014111719: 48: 02 ****** BEGINTRANSACTIONDECLARE @ ReturnCodeINTSELECT @ ReturnCode0 ****** Object: Job

Use SQL statements to create steps, alarms, plans, notifications, and targets to execute a job. No USE [msdb] GO/****** Object: Job [DatabaWorkFlowSql] Script Date: 19:48:02 *****/begin transactiondeclare @ ReturnCode INTSELECT @ ReturnCode = 0/***** Object: Job

Use SQL statements to create steps, alarms, plans, notifications, and targets to execute a job. <无>

USE [msdb] GO/****** Object: Job [DatabaWorkFlowSql] Script Date: 19:48:02 *****/begin transactiondeclare @ ReturnCode INTSELECT @ ReturnCode = 0/****** Object: JobCategory [[Uncategorized (Local)] Script Date: 19:48:02 *****/if not exists (SELECT name FROM msdb. dbo. syscategories WHERE name = n' [Uncategorized (Local)] 'AND category_class = 1) BEGINEXEC @ ReturnCode = msdb. dbo. sp_ad D_category @ class = n' job', @ type = n' local', @ name = n' [Uncategorized (LOCAL)] 'If (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @ jobId BINARY (16) EXEC @ ReturnCode = msdb. dbo. sp_add_job @ job_name = n' databaworkflowsql', @ enabled = 1, @ policy_level_eventlog = 1, @ policy_level_email = 1, @ reply = 1, @ policy_level_page = 1, @ delete_level = 0, @ description = n' description of log transmission. 1. create a table in TableInfo. insert data 3. back up the database ', @ category_name = n' [Uncategorized (Local)]', @ owner_login_name = n' ROBORTLY \ yon', @ yy_email_operator_name = n' luoyong ', @ yy_netsend_operator_name = N 'luoyong ', @ yy_page_operator_name = N 'luoyong', @ job_id = @ jobId OUTPUTIF (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO QuitWithRollback/****** Object: Step [createTable] Script Date: 19:48:02 ******/EXEC @ ReturnCode = msdb. dbo. sp_add_jobstep @ job_id = @ jobId, @ step_name = N 'createtable', @ step_id = 1, @ token = 0, @ on_success_action = 3, @ token = 0, @ on_fail_action = 2, @ on_fail_step_id = 0, @ retry_attempts = 0, @ retry_interval = 0, @ OS _run_priority = 0, @ subsystem = n'tsql', @ command = n'create table WorkFlowTable (wfid int, datas nvarchar (10) ', @ database_name = N 'tableinfo', @ flags = 0IF (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO QuitWithRollback/****** Object: Step [insertValues] Script Date: 19:48:02 ******/EXEC @ ReturnCode = msdb. dbo. sp_add_jobstep @ job_id = @ jobId, @ step_name = N 'insertvalues ', @ step_id = 2, @ response = 0, @ on_success_action = 3, @ response = 0, @ on_fail_action = 2, @ on_fail_step_id = 0, @ retry_attempts = 0, @ retry_interval = 0, @ OS _run_priority = 0, @ subsystem = N 'tsql', @ command = N 'insert into workflowTable values (1, ''luoyong'') ', @ database_name = n' tableinfo', @ flags = 0IF (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO QuitWithRollback/****** Object: Step [Backup] Script Date: 19:48:02 ******/EXEC @ ReturnCode = msdb. dbo. sp_add_jobstep @ job_id = @ jobId, @ step_name = n' backup ', @ step_id = 3, @ region = 0, @ on_success_action = 1, @ on_success_step_id = 0, @ on_fail_action = 2, @ on_fail_step_id = 0, @ retry_attempts = 0, @ retry_interval = 0, @ OS _run_priority = 0, @ subsystem = N 'tsql ', @ command = n' backup database [TableInfo] TO [luoyongImportant] with noformat, NOINIT, NAME = n' TableInfo-full database backup '', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO ', @ database_name = n' tableinfo', @ flags = 0IF (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ ReturnCode = msdb. dbo. sp_update_job @ job_id = @ jobId, @ start_step_id = 1IF (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ ReturnCode = msdb. dbo. required @ job_id = @ jobId, @ name = N 'plan', @ enabled = 1, @ freq_type = 1, @ freq_interval = 0, @ freq_subday_type = 0, @ freq_subday_interval = 0, @ freq_relative_interval = 0, @ freq_recurrence_factor = 0, @ active_start_date = 20141117, @ active_end_date = 99991231, @ active_start_time = 194600, @ active_end_time = 235959, @ schedule_uid = n' 10456071-9a44-4c6a-82b7-7243899c4db6 'IF (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ ReturnCode = msdb. dbo. sp_add_jobserver @ job_id = @ jobId, @ server_name = n' (local) 'IF (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@ TRANCOUNT> 0) ROLLBACK TRANSACTIONEndSave: GO