First of all, Ms Server, because I am also a novice, so first chose to use Enterprise Manager for task settings, and then export the design completed task script, This allows you to create new tasks directly in other databases by executing scripts (you need to modify some of these task names and database name equivalents), and here's a specific procedure and a task script that you get:
1. Ensure that the "SQL Server Agent Service" (that is, agent services) starts MS server
2. Open Enterprise Manager-> connect the data server-> "Jobs" under the SQL Server Agent node, select "New Job", and in the detailed task page we'll see a lot of settings: include "General", "Step," "Plan," "Alert," " Notifications "and" targets ", where we can perform very detailed task settings, because what I have to do is simply to execute one of the stored procedures in the database on time, so I simply set the contents of the general, the steps, and the plan three, in which we set the database operations and sequences we want to do , and then through the "plan" to set the specific operation plan, compared to directly write SQL statements, it is easier, more suitable for beginners.
3. Right-click to select the job we just created, choose Script Job as-> create to, and choose to store the script in the file or open it directly through the editor, and then save it. We can see that the system-generated script looks rather complicated because it sets all of the parameters in it and may omit many of the default parameters through SQL scripts, but we can clearly see several key steps in job creation. The SQL script for the personal example is appended as follows. The following streamlined code is as follows:
Copy Code code as follows:
DECLARE @jobId BINARY (16)
--create the Job
EXEC msdb.dbo.sp_add_job @job_name = ' Update table of Sys_pageinfo ',
@job_id = @jobId OUTPUT
--create the step of the job
EXEC msdb.dbo.sp_add_jobstep @job_id = @jobId,
@step_name = ' Just one step ',
@subsystem = ' TSQL ',
@command = ' EXEC dbo. Batchupdatearchlisttotalnum ',
@database_name =n ' exchanger_2 '
--create the schedual of the job
EXEC msdb.dbo.sp_add_jobschedule @job_id = @jobId,
@name = ' The Schedual ',
@freq_type = 4,
@freq_interval = 1,
@active_start_time = 10000
--create the server of the job
EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N ' (local) '
Let's look at how the job is created in Oracle, and this time we'll create it using SQL scripts, and some common SQL statements are as follows:
First, view the tasks in the database
SELECT * FROM User_jobs
Ii. Delete the tasks specified in the database
Begin
Dbms_job.remove (50);
End
third, create a task that performs every 5 minutes
DECLARE
Jobid number;
BEGIN
SYS. Dbms_job. SUBMIT (Job => Jobid,
What => ' Batchupdatearchlisttotalnum (); ',//stored procedure if there are no arguments, you can omit the parentheses, but you cannot omit the semicolon
Next_date => Sysdate,
Interval => ' SYSDATE+1/24/12 ');
COMMIT;
End;
Attached: SQL Server task script:
Copy Code code as follows:
Use [msdb]
Go
/****** object: Job Update script Date: 05/12/2009 18:14:14 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** object: jobcategory [Database Maintenance] Script Date: 05/12/2009 18:14:14 ******/
IF not EXISTS (SELECT name from msdb.dbo.syscategories WHERE name=n ' Database maintenance ' and Category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class =n ' JOB ', @type =n ' local ', @name =n ' Database maintenance '
IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO Quitwithrollback
End
DECLARE @jobId BINARY (16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name =n ' job name ',
@enabled = 0,
@notify_level_eventlog = 0,
@notify_level_email = 0,
@notify_level_netsend = 0,
@notify_level_page = 0,
@delete_level = 0,
@description =n ' no description. ',
@category_name =n ' Database maintenance ',
@owner_login_name =n ' sa ', @job_id = @jobId OUTPUT
IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO Quitwithrollback
/****** object: Step [Update sys_pageinfo table content] Script Date: 05/12/2009 18:14:15 ******/
EXEC @ReturnCode = Msdb.dbo.sp_add_jobstep @job_id = @jobId, @step_name =n ' Operation step name ',
@step_id = 1,
@cmdexec_success_code = 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 ' EXEC dbo. Batchupdatearchlisttotalnum ',
@database_name =n ' exchanger_2 ',
@database_user_name =n ' dbo ',
@flags =0
IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO Quitwithrollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO Quitwithrollback
EXEC @ReturnCode = Msdb.dbo.sp_add_jobschedule @job_id = @jobId, @name =n ' plan name ',
@enabled = 1,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_start_date = 20090512,
@active_end_date = 99991231,
@active_start_time = 0,
@active_end_time =235959
IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO Quitwithrollback
EXEC @ReturnCode = Msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N ' (local) '
IF (@ @ERROR <> 0 OR @ReturnCode <> 0) GOTO Quitwithrollback
COMMIT TRANSACTION
GOTO Endsave
Quitwithrollback:
IF (@ @TRANCOUNT > 0) ROLLBACK TRANSACTION
Endsave:
