Use the T_ SQL script to create a SQLServer2000 background scheduled job

Author: Jiang Yong http://idoorsoft.51.net
In the database application system, making full use of the functions of the database backend server can simplify the work of the client front-end, reduce the network load, and make the overall system design more reasonable, it is easy to maintain, transplant, and upgrade. It is often used in many database applications to schedule task tasks in the background. It is used in combination with the stored procedure.
In SQL Server2000, you can manually create a background scheduled task job in Enterprise Manager step by step, but this is both troublesome and not easy to publish, so this article provides a way to create a job using a T-SQL script.
The following three stored procedures in the SQL Server2000 msdb system library are required to complete job creation. Before that, enable the SQLServerAgent service of the database instance. After SQL Server is installed, the service is not started by default.
By the way, SQL Server has an obvious concept of "instance" in Version 2000, and there is no clear instance in version 7.0, therefore, a default instance is created when SQL Server 7.0 is installed by default to be compatible with SQL Server. If you create an instance by default, the instance name is blank. What? You don't know what an instance is "? I personally want to find some information and see that there are instances and tablespaces in both El and sybase. Therefore, my name is SQL Server before SQL Server2000, which is a desktop database.
Go to the topic. The procedure is "job"> "job scheduling"> "job step". The details are as follows:
1. Use sp_add_job to add a new job executed by the SQLServerAgent service.
2. Use sp_add_jobschedule to create job scheduling.
3. Use sp_add_jobstep to add a step (operation) to the job.
The following uses the script in the ticket system of the bus terminal as an example to show the actual example. Before looking at the example, please take a look at the help of the above three system stored procedures. In this example, a custom stored procedure "tksp_bakdata" is used to process the ticket sales data before the current day (you only need to know that it is a custom stored procedure ).
Example 1: Process ticket sales data at 00:30 every day
Www.111cn.net
Use msdb
EXEC sp_add_job @ job_name = 'TK _ bakdata ',
@ Enabled = 1,
@ Description = 'process ticket sales data at Everyday ',
@ 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 ',