SQLServer2000 Background Job Plan

Server|sqlserver| backstage in the database application system, the full use of the database backend server function can simplify the client front desk work, but also reduce the network load, while making the whole system design more reasonable, easy to maintain the transplant and upgrade, Background scheduled task jobs are often used in many database applications, and are of course used in conjunction with stored procedures.
In SQL server2000, you can manually set up a background scheduled task job in Enterprise Manager in a step-by-step manner, but this is cumbersome and inconvenient to publish, so this article gives you a way to create a job using T-SQL script.
You need a stored procedure in the following three SQL server2000 msdb system libraries to complete the job creation, before you open the SQLServerAgent service for the database instance, which is not started by default after SQL Server installation.
Incidentally, SQL Server has an obvious "instance" concept in version 2000, and there is no explicit instance in version 7.0, so a default instance was created at SQL server2000 default installation for compatibility with SQL Server 7.0. If you are creating an instance by default, the instance name is empty. What You don't know what an "example" is? Individuals to find some information to see, Oracel, Sybase have instances and table space, so I called SQL server2000 SQL Server for the desktop database.
To get to the point, the steps are "job"-〉 "job Schedule"-〉 "job steps" as follows:

1. Use Sp_add_job to add new jobs that are performed by the SQLServerAgent service.
2, use sp_add_jobschedule to create job scheduling.
3. Use Sp_add_jobstep to add a step (action) to the job

The following is an example of a script in a ticketing system for a bus passenger station, and before you look at the example, look at the help of the three system stored procedures above. In the example, a custom stored procedure, "Tksp_bakdata", is used to process ticketing data prior to the day (just know it is a custom stored procedure).

Example 1: Processing ticket data daily 0:30
Www.knowsky.com

Use msdb
exec sp_add_job @job_name = Tk_bakdata,
@enabled = 1,
@description = handle ticketing Data 00:30 daily,
@start_step_id = 1,
@owner_login_name = Tkuser
exec sp_add_jobserver @job_name = Tk_bakdata
Go

exec sp_add_jobschedule @job_name = Tk_bakdata,
@name = bakdata003000,
@freq_type = 4,
@freq_interval = 1,
@active_start_time = 003000
Go

exec sp_add_jobstep @job_name = Tk_bakdata,
@step_name = Bakdata,
@subsystem = TSQL,
@command = exec Tksp_bakdata,
@database_name =ticket
Go

Example 2: Processing ticket data every day when SQL Server starts up, which ensures that ticket data is processed on the server that needs to be shut down daily.

Use msdb
exec sp_add_job @job_name = Tk_bakdata2,
@enabled = 1,
@description = Handle ticket data on daily SQL Server startup
@start_step_id = 1,
@owner_login_name = Tkuser
exec sp_add_jobserver @job_name = tk_bakdata2
Go

exec sp_add_jobschedule @job_name = Tk_bakdata2,
@name = Bakdatastart,
@freq_type = 64
Go

exec sp_add_jobstep @job_name = Tk_bakdata2,
@step_name = Bakdatastart,
@subsystem = TSQL,
@command = exec Tksp_bakdata,
@database_name =ticket
Go