If you need timing in SQL Server or every time you execute a stored procedure or a SQL statement within 3200 characters,
Can be implemented with management -> SQL Server Agent -> Job.
1. Management -> SQL Server Agent -> Job (Right click on the mouse) -> New Job ->
2, new job properties (regular) -> name [custom this job name] -> The box is hitted in the box ->
Category Also available can also be used by default [unscapable (local)] -> owner defaults to log in to SQL Server users [also choose other login] ->
Description [Fill in this work Detailed Description];
[Create a job classification:
SQL Server Agent -> Job -> Right click All Tasks -> Add, Modify, Delete]
3, new job properties (step) -> New -> Step Name [Custom First Step Name] -> Type [Transact-SQL (TSQL) script] ->
Database [Database] -> Command
[If it is a simple SQL to write directly, you can also use the open button to enter a written * .sql file.
If you want to perform a stored procedure, fill in
EXEC P_PROCEDURE_NAME V_PARAMETER1, [V_Parameter2 ... v_parametern]
]
-> Determine
(If there are multiple steps, you can call the next new button again; you can also insert, edit, delete it), edit, delete it);
4, build the job attribute (scheduling) -> New Scheduling -> Name [Custom Scheduling Name] -> The box is hook number -> Scheduling -> Repeated appearance ->
Change [Scheduling Table] -> OK
(If you want to save this job, don't schedule you can remove the hook in the enabled box);
5. Construction of job properties (notifications) -> Use the default notification method to "When the job failed, Write Windows Application System Log] -> OK.
Some SQL Server knowledge related to the operation:
The SQLServerAgent service must run normally, starting its NT login user to match the NT login user of the SQL Server database.
Point job Right click to view the history of job execution, or immediately start the job and stop job.
Recently, when I saw a history history, I found a history of the history record. There were fewer history records for the records of job records. How can some jobs reserve some time according to their needs, such as retaining a month history recording.
I have seen the online help documentation of SQL Server, which is introduced:
In Management -> SQL Server Agent -> Right-click Select Properties -> Job System -> Limit Job History Log Size ->
The maximum size of the job history log (number of lines) defaults to 1000 If a machine has a lot of jobs, it must improve it, for example 100,000
The maximum number of rows of each job history log is that 100 If the job is executed twice a day, you need to keep a month log, you can set it to 60
There is a mutual restriction between them, we can change it according to your needs.
If the SQL Server server changes the machine name, management is the job created when the old name is established, it may encounter an error 14274: Unable to add, update, or delete the job initiated from the MSX server (or step or scheduling)
I saw Microsoft documents:
http://support.microsoft.com/default.aspx?scid=kb;n-us; 281642
Say the original server name in the originating_server field in the msdb..sysjobs in the SQL Server 2000 system.
24x7 is definitely cautioned by the system that cannot be changed back to the name of Microsoft documentation.
So think, can MSDB..sysjobs can update originating_server fields into new server names now?
USE MSDB
Select * from sysjobs
Locate the originating_server field or the JOB_ID of the old server, then execute the update statement: update sysjobs set Originating_server = 'new_server_name'
WHERE JOB_ID = 'B23BBEBE-A3C1-4874-A4AB-0E2B7CD01E14'
(The number of rows affects is 1 line)
This will be added, updated, or deleted those who have erect 14274.
If you want to migrate your homework from a machine to another, you can keep the script for creating jobs and run on another machine.
Export all job creation script procedures:
Management -> SQL Server Agent -> Job (Right-click) -> All Tasks -> Generate SQL Scripts -> Save to a SQL file under the operating system
Export a creation script procedure for a job:
Manage -> SQL Server Agent -> Job -> Holding Job (Right-click) -> All Tasks -> Generate SQL Scripts -> Save to an SQL file under OS
Then run the SQL script that has just been saved on the destination server.
(If the user or the user who is reminded does not exist, it will be wrong;
We need to establish a related Windows user or SQL Server database login on the destination server.
You can also modify the script of the job created, replace the user that does not exist on the destination server into an existing user.
If the physical file directory of the generated log does not exist, you should also do related modifications, such as D: / zone to F: / district, etc.
The @Command command in the string will also be wrong, you can remove it)
