Home > Article > Backend Development > Use T_SQL script to create SQLServer2000 background scheduled job task_PHP tutorial
Use T_SQL script to create SQLServer2000 background scheduled job task_PHP tutorial
- WBOYOriginal
- 2016-07-13 17:00:451106browse
Author: Jiang Yong http://idoorsoft.51.net
In the database application system, making full use of the back-end server function of the database can simplify the work of the client front-end, and can also reduce the load of the network, and at the same time make The entire system design is more reasonable, making it easier to maintain, transplant and upgrade. Background scheduled task jobs are often used in many database applications, of course in conjunction with stored procedures.
In SQL Server2000, you can manually create a background scheduled task job in the Enterprise Manager step by step, but this is cumbersome and not convenient for publishing. Therefore, this article gives a method 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 the creation of the job. Before doing this, please enable the SQLServerAgent service of the database instance. This service is not enabled by default after SQL Server is installed.
By the way, SQL Server only had an obvious concept of "instance" in version 2000. There was no clear instance in version 7.0, so a default instance was created during the default installation of SQL Server 2000. This is for the sake of peace of mind. SQl Server 7.0 is compatible. If you create an instance by default, the instance name will be empty. What? You don’t know what an “instance” is? I personally went to find some information and found out that Oracle and Sybase both have instances and table spaces, so I called SQL Server before SQL Server 2000 a desktop database.
Getting to the point, the steps are "Job" -> "Job Scheduling" -> "Job Step", 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 schedule.
3. Use sp_add_jobstep to add a step (operation) to the job
The following takes the script in the bus terminal ticketing system as an example to give a practical example. Before looking at the example, please take a look at the above three Help for system stored procedures. In the example, a custom stored procedure "tksp_bakdata" is used, its function is to process the ticket sales data before the day (just know that it is a custom stored procedure).
Example 1: Process ticket sales data at 0:30 every day
www.knowsky.com
use msdb
EXEC sp_add_job @job_name = 'tk_bakdata',
@enabled = 1,
@description = 'Process ticketing data at 00:30 every day',
@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',