Create a job on SQL server

Method for setting a scheduled job for SQL: If you need to regularly execute a stored procedure or SQL statement of less than 3200 characters at intervals in SQL Server, you can use "manage"> "SQL Server proxy"> "job.

1. Choose manage> SQL Server proxy> job (right-click)> create job
2. Create job attributes (General) -> name [customize the name of this job]-> the Enable box contains a check mark-> the default [unclassified (local)] can be selected or available in the Category field. -> the owner logs on to SQL Server by default. [Other Logon options are also available]-> description [fill in the detailed description of this job];
To create a job category, choose SQL Server proxy> job> right-click all tasks> Add, modify, and delete
3. Create a job property (STEP)-> New-> step name [custom Step 1 name]-> type [Transact-SQL (TSQL) script]-> database [database to be operated]-> Command [if it is a simple SQL statement, write it directly. You can also open the button and enter a written *. SQL. To execute the stored procedure, enter exec p_procedure_name v_parameter1, [v_parameter2... V_parameterN]-> OK (if there are multiple steps, you can call the new button below again; you can also insert, edit, or delete multiple existing steps );
4. Create job attributes (scheduling) -> Create scheduling-> name [custom scheduling name]-> enable the box with a check mark-> scheduling-> repeatedly appear-> change [scheduling schedule]-> OK (if you only need to save this job, if you do not do this regularly, you can remove the check mark in the enabled box );
5. Create job attributes (notifications)-> use the default notification method [write Windows application system logs when a job fails]-> OK.
Some SQL Server knowledge related to job execution:
The SQL Server Agent service must run properly. The NT login user who starts it must be consistent with the NT login user who starts the SQL Server database;
Right-click a job to view the history of the job execution, or immediately start and stop the job.
Recently, when viewing the job history, we found that some jobs have many historical records and some have fewer historical records. How can I keep some jobs for a period of time according to their own needs? For example, keep a one-month history. I read the SQL Server online help documentation, which introduces:
Choose "manage"> "SQL Server proxy"> "right-click" properties ">" job system ">" limit the size of job history logs ".
The maximum size (number of rows) of job history logs. The default value is 1000. If a machine has a large number of jobs, you must increase the number of jobs, for example, 100000.
The maximum number of lines in the history logs of each job. The default value is 100. If the job is executed twice a day and you need to keep the log for one month, you can set it to 60
There is a mutual constraint between them. We can change it according to our own needs.
If the SQL Server name is changed to the machine name, it may be encountered when the management is a job created when the old name is used. Error 14274: unable to add, update, or delete a job (or its steps or scheduling) initiated on the MSX server ). I have read Microsoft's document: http: // support.microsoft.com/default.aspx? Scid = kb; en-us; 281642 indicates that the original Server name is stored in the originating_server field in msdb... sysjobs in SQL Server 2000. The 24x7 system cannot change the name as described in the Microsoft document above. So I thought, could msdb... sysjobs update originating_server field to the new server name currently in use?

Use msdb
Select * from sysjobs

Find the originating_server field or the job_id of the old server, and then execute the update statement:

Update sysjobs set originating_server = 'new _ server_name'
Where job_id = 'b23bbebe-A3C1-4874-A4AB-0E2B7CD01E14'

(The number of affected rows is 1)
In this way, you can add, update, or delete jobs with an error 14274 error.

If you want to migrate a job from one machine to another, you can keep the script for creating the job and run it on the other machine.
To export a script for all jobs, follow these steps:
Manage-> SQL Server proxy-> job (right-click)-> all tasks-> Generate SQL scripts-> Save to an SQL file in the operating system

To export a script for a job, follow these steps:

Manage-> SQL Server proxy-> job-> select the job to be transferred (right-click)-> all tasks-> Generate SQL script-> Save to an SQL file under OS

Then, run the SQL script for creating the job saved just now on the target server. (If the user who created the job or the user who reminded him does not exist, an error will occur. We need to create a WINDOWS user or SQL Server database logon on the target Server, you can also modify the script for creating a job to replace a user that does not exist on the target server with an existing user. If the directory of the physical file that generates the log does not exist, make relevant modifications, such as d: \ To f: the @ command of a string such as \ zone contains the separator "go", which can be removed)

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/lxl_sports/archive/2009/11/28/4895433.aspx