SQl Job aspects

Enterprise Manager
--Management
--sql Server Agent
--Right-click Job
--New job
--Enter the job name in the "General" entry
--"Step" item
--New
--Enter the step name in step name
--SELECT "Transact-SQL script (TSQL)" in "type"
--"Database" Select the database that executes the command
--Enter the statement to execute in the command:
Update Base Set flag=0 where DateDiff (Dd,date,getdate ()) =0

-Determine
--"Dispatch" item
--New schedule
--Enter the schedule name in "name"
--Select your job execution schedule in the schedule type
--If "recurring" is selected
--point "change" to set your schedule to one day at a time


The SQL Agent service is then started and set to start automatically, otherwise your job will not be executed

Setup method:
My Computer--control Panel--management tool--service--right-sqlserveragent--Property--Startup Type--select "Auto Start"--OK.

In the application system of database, full use of the function of the backend server of the database can simplify the work of the client foreground, reduce the load of the network, make the whole system design more reasonable, facilitate the maintenance migration and upgrade, and the background planning task work is often used in many database applications. It is 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 it is cumbersome and inconvenient to publish, so this article gives you a way to create a job using T-SQL scripts.
The following three SQL Server2000 the stored procedures in the msdb system library are required to complete the job creation, before the SQLServerAgent service for the DB instance is opened, and the service is not started by default after SQL Server installation.
By the way, SQL Server has a clear "instance" concept in version 2000, there are no explicit instances in version 7.0, so a default instance is created when SQL Server2000 is installed by default, for compatibility with SQL Server 7.0. If you are creating an instance by default, the instance name is empty. Why You don't know what "instance" is? Individual to find some information to see, Oracel, Sybase have instances and table space, so I called SQL Server2000 before the SQL Server for the desktop database.
To get to the point, the step is "job"-〉 "job Schedule"-〉 "job step", as follows:

1. Use Sp_add_job to add new jobs performed by the SQLServerAgent service.
2. Create job schedule using Sp_add_jobschedule.
3. Use Sp_add_jobstep to add a step (action) to the job


The following is an example of a script in the bus terminal ticketing system, and before looking at the example, take a look at the help of the above three system stored procedures. In the example, a custom stored procedure "Tksp_bakdata" is used, and its function is to process the ticket data prior to the current day (just know that a custom stored procedure is OK).


Example 1: Daily 0:30 processing of ticket data
Www.knowsky.com

Use msdb
EXEC sp_add_job @job_name = ' Tk_bakdata ',
@enabled = 1,
@description = ' daily 00:30 processing of ticket data ',
@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: The daily processing of ticketing data when SQL Server is started, so that the ticketing data can be processed on servers that need to be shut down every day.

Use msdb
EXEC sp_add_job @job_name = ' Tk_bakdata2 ',
@enabled = 1,
@description = ' processing ticket data on 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

SQl job aspect (RPM)