SQL scheduled execution of Stored Procedures

SQL scheduled execution Stored Procedure

-- Create a job

-- Create job exec msdb .. sp_add_job @ job_name = 'data'

-- Create a job step declare @ SQL varchar (800), @ dbname varchar (250) select @ SQL = 'exec p_proc' -- stored procedure to be periodically executed, @ dbname = db_name () -- Name of the database for processing data

Exec msdb .. sp_add_jobstep @ job_name = 'data', @ step_name = 'data synchronization', @ subsystem = 'tsql', @ database_name = @ dbname, @ command = @ SQL, @ retry_attempts = 5, -- number of retries @ retry_interval = 5 -- Retry Interval

-- Create the scheduled EXEC msdb .. sp_add_jobschedule @ job_name = 'data', @ name = 'schedule ', @ freq_type = 44? -- 4 every day, 8 weeks, 16 months @ freq_interval = 14? -- Job execution days @ freq_subday_type = 04? -- Repeated execution? 0x1 at the specified time, 0x4 minutes, 0x8 hours @ freq_subday_interval = 1, -- recurrence @ freq_recurrence_factor = 04? -- Repeated execution, set to 1; otherwise, set to 0 @ active_start_time = 00000 -- start execution at 0

If you want to regularly execute stored procedures in SQL Server or execute a stored procedure or SQL statements of less than 3200 characters at intervals, you can use "manage"> "SQL Server proxy"> "job.

1,Manage-> SQL Server proxy-> job (right-click)-> create job->

2,Create a job property (general)-> name [customize the name of this job]-> the Enable box contains a check mark->

You can select or use the default [unclassified (local)]-> the owner is the default logon SQL Server user [or other logon options]->

Description [fill in the detailed description of this job];

[Procedure for creating a job category: SQL Server proxy-> job-> right-click all jobs-> Add, modify, and delete jobs]

3,Create job properties (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, you can directly write it in, you can also open the button and enter a written *. to execute the stored procedure in the SQL file, 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, or you can insert, edit, or delete multiple existing steps );

4,Create job attributes (scheduling)-> Create scheduling-> name [custom scheduling name]-> enable the check box-> schedule-> repeatedly appear->

Change [scheduling schedule]-> OK (if you only need to save this job, you can remove the check mark in the enabled box if you do not need to do so regularly );

5,Create job properties (notification)-> use the default notification method [write logs to Windows application system when the job fails]-> OK.

Some SQL Server knowledge related to job execution:

The SQLSERVERAGENT 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 some jobs be retained for a period of time according to their own needs, for example, retaining the history of a month.

I read the online help document of SQL Server, which introduces:

Choose "manage"> "SQL Server proxy"> "right-click" properties ">" job system ">" limit the size of job history logs ">

The maximum job history log size (number of rows) is 1000 by default. If a machine has a large number of jobs, you must increase it, for example, 100000

The maximum number of lines in the history logs of each job is 100 by default. if the job is executed twice a day, you need to keep the logs for one month, which can be set to 60.

There is a mutual constraint between them. We can change it according to our own needs.

If you need to regularly execute a stored procedure or SQL statements of less than 3200 characters in SQL Server, you can use management> SQL Server proxy> job.

If the SQL Server has changed its machine name, it may encounter

Error 14274: unable to add, update, or delete a job initiated on the MSX server (or its steps or scheduling)

After reading the Microsoft documentation, 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, choose "manage"> "SQL Server proxy"> "job"> "select the job to be transferred (right-click) -> all tasks-> Generate SQL scripts-> Save to an SQL file in the 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 details the stored procedure of SQL scheduled execution, hoping to help you.

[Edit recommendations]

1. An easy way to obtain all SQL Server 2005 stored procedures
2. Two Methods for calling stored procedure in ASP. NET
3. Experience in writing SQL Server Stored Procedures
4. Summary of java's experience in calling oracle stored procedures
5. Oracle Stored Procedure experience