Как создать job в ms sql

MS SQL Server Создать Job

Что необходимо: перенести данные из основной базы в хранилище. Что хочется: создать Job, который будет переносить данные.

Нашёл, как обращаться к другому серверу, но в моей таблице sys.servers сервер один. Вопрос: есть ли в принципе такая возможность переноса данных, или нужно это делать кодом?

Отслеживать
задан 10 дек 2018 в 21:20
718 3 3 серебряных знака 13 13 бронзовых знаков

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

Вам нужно прилинковать все сервера к которым вы собираетесь обращаться. Есть два способа создания связанного сервера:

Через UI SSMS

Здесь все сводится к тому что надо открыть SSMS, найти в обозревателе объектов пункт Linked Servers, и заполнять поля следуя инструкции из статьи ниже.

Скриптами

1) Пример скрипта для создания свзянного сервера с экземпляром SRVTEST\SQLTEST .

USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'SRVTEST\SQLTEST', @srvproduct=N'SQL Server' ; GO 

2) Дальше если вы используюте виндовую авторизацию вам необходимо прокинуть логин входа на связанный сервер

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SRVTEST\SQLTEST', @locallogin = NULL , @useself = N'True' ; GO 

Если авторизация SQL Server то просто создаем такой же логин на сязанном сервере.

EXEC sp_addlinkedsrvlogin @rmtsrvname = N'SRVTEST\SQLTEST', -- имя должно быть таким же как в sp_addlinkedserver @useself = N'False', @rmtuser = 'MyUsername' @rmtpassword = 'MyPassword'; 

Create SQL Server Job to Run Periodic Tasks Automatically

Executing periodic tasks using SQL Server Agent by creating SQL jobs on SQL Server is one of the most powerful tools shipped with SQL Server data platform. SQL Server Management Studio serves the wizard for T-SQL developer and database administrators to create new SQL Server job in a few steps and configure properties like execution times by assigning a job schedule, logging job execution results, etc.

In order to create a new SQL job, first of all launch SQL Server Management Studio and connect to SQL Server instance.
On Object Explorer window expand node SQL Server Agent.
Under Jobs node, there are jobs already created on that SQL Server instance.

To create new SQL Server job, right click on the Jobs node and from context menu choose «New Job. «

In General tab, type a descriptive short name for the new SQL job in Name textbox.
Additionally you can type informative detailed information in Description textbox.
Then press OK for next step.

In Steps tab, SQL developers and administrators define the task which will be executed.
Click on New button to create a new SQL job step.

Type a step name.
I chosed Transact-SQL script (T-SQL) as step type.
T-SQL sql step type enables developers to execute SQL scripts like executing a stored procedure, etc. on the specified database.

For example, in Command text area I paste below SQL code.
In this tutorial, transfer_data is the stored procedure name.

Exec transfer_data

It is possible to validate the SQL codes by pressing the Parse button to see if there is an error preventing SQL job step to execute.
If everything is completed, press OK to continue with next step.

In the next step, the Schedules for the related SQL Server job is created and defined. You can click on New. to create a new schedule for periodic execution.

In the New Job Schedule you can define details like frequency, execution days, time, start date and end date if an execution period exists.

If you have completed the SQL job schedule, press OK to complete the job creation.

After SQL job is created, in the Object Explorer window of the SQL Server Management Studio you can see the recently created SQL job.

As seen in below screenshot, it is also possible to launch SQL job manually by right-click on the SQL Server job and choose «Start Job at Step. «

Планирование заданий агента SQL Server

Какова бы ни была целеустремленность и квалификация сотрудника, не существует людей, способных работать каждый день и год за годом по 24 часа в сутки. И тем не менее, словно именно это ежедневно требуется от администраторов баз данных: никогда не допускать промахов, быть готовым к развертыванию и запуску программных продуктов по первому требованию и выполнять важнейшие задачи обслуживания в любое время суток. Однако мы все же можем выполнить эти требования благодаря службе агента SQL Server и ее компонентам. Агент SQL Server представляет собой систему планирования, обработчик заданий, средство общения и круглосуточного администрирования для Microsoft SQL Server. Вы сможете убедиться в этом сами, ознакомившись с предлагаемым обзором данного инструмента.

Компоненты агента SQL Server

Агент SQL Server выглядит как единая служба, но состоит из нескольких компонентов:

• планирование заданий;
• уведомления и предупреждения;
• операторы;
• журнал ошибок;
• серверы-посредники.

Тема агента SQL Server весьма обширна, и потребуется несколько статей, чтобы рассмотреть каждую грань его функциональности. В первой статье мы сосредоточимся на компоненте планирования заданий.

Агент SQL Server как система планирования заданий

Когда речь идет о необходимости круглосуточного присутствия, я имею в виду функциональность агента SQL Server как планировщика заданий. Агент SQL Server позволяет создавать задания различной сложности, состоящие из одного или нескольких «шагов», на каждом из которых можно выполнять команды на различных языках, вплоть до Transact-SQL, и наборы ориентированных на SQL задач (запросы или команды SQL Server Analysis Services, пакеты SQL Server Integration Services), а также сценарии PowerShell, VB Script и задачи операционной системы. Все шаги могут быть объединены в цепочку для последовательного выполнения или запускаться в зависимости от успеха, неудачи или завершения каждого шага в цепочке. Кроме того, вы можете отправлять предупреждения, сохранять выходные данные шага и создавать файлы журнала на каждом шаге. Выполнение задач можно запланировать согласно расписанию. Как шаги, так и собственно задание могут быть настроены для рассылки предупреждений и уведомлений. Можно даже настраивать задания для выполнения на удаленных целевых объектах.

Рассмотрим для примера процесс создания простого многошагового задания, которое усекает таблицу в базе данных после создания разностной резервной копии, чтобы убедиться в возможности восстановления в случае внесения ошибочных изменений.

Функции для создания задания можно найти с помощью обозревателя объектов в среде Microsoft SQL Server Management Studio (SSMS). Разверните узел сервера, и вы увидите агент SQL Server как подузел (см. экран 1).

Экран 1. Компоненты агента SQL Server

Щелкните правой кнопкой мыши на разделе Jobs («Задания») и выберите в контекстном меню пункт New Job («Создать задание»). Вы увидите форму, представленную на экране 2.

Экран 2. Создание нового задания

На данном этапе я заполнил необходимые поля, чтобы сделать то, что требуется на первом шаге: создать разностную резервную копию. По умолчанию владелец задания — текущий зарегистрированный пользователь. Задание принадлежит моей учетной записи в AD, но это не значит, что у него тот же контекст выполнения. По умолчанию задание выполняется в контексте учетной записи службы агента SQL Server. Это означает, что нужно предоставить учетной записи службы все права, необходимые для выполнения требуемых задач. Кроме того, если в выполнение задачи вовлечены какие-либо локальные или удаленные диски, служба агента SQL Server (или выполняющая учетная запись, так как это определяемый параметр) должна иметь права для доступа к соответствующим томам.

На каждом шаге мы видим два окна: общих настроек и дополнительных параметров. Общие настройки показаны на экране 2, и в этом окне можно объявить, какую команду следует выполнить. В окне дополнительных параметров представлены критерии, согласно которым происходят переходы от одного шага к другому, а также регистрируемая информация о шаге (см. экран 3).

Экран 3. Окно дополнительных параметров

Необходимо сделать так, чтобы шаг усечения (следующий в цепочке) выполнялся только в случае успеха резервного копирования. Если резервное копирование прошло неудачно, то задание должно завершиться ошибкой. Я не пытаюсь повторить этот шаг, хотя такая возможность существует. Кроме того, я хочу записать выходные данные шага (вы увидите их на вкладке сообщений окна запроса в SSMS, если выполните программный код шага) в файл, хранящийся в C:\temp. Этот файл, если он существует, будет перезаписан, и я хочу сохранить выходные данные шага в журнале. Обычно я записываю выходные данные двумя способами, так как в самом файле выходные данные не обрезаются, а для журнала шага действуют ограничения по размеру строки. Вы также видите, что можно настроить задачу на выполнение другим пользователем, а не только учетной записью службы по умолчанию для агента SQL Server.

Теперь мы повторяем процесс для шага усечения с подходящими значениями (см. экран 4).

Экран 4. Создание шага усечения базы данных

На шаге усечения назначается контекст базы данных, указывающий на базу данных, в которой планируется выполнить усечение таблицы. Я мог бы оставить ее как основную базу данных по умолчанию, при условии что команда содержит имя базы данных и схему (см. экран 5).

Экран 5. Настройка шага усечения базы данных

Для шага усечения я готов повторить попытку до трех раз с ожиданием в течение одной минуты между попытками. Если шаг завершается успешно, то нужно, чтобы и задание завершилось как успешное. Аналогично, если шаг завершается неудачей, то неудачно и задание. Это учитывается при настройке предупреждений и уведомлений о заданиях. И вновь я хочу записать выходные данные в файл, созданный или указанный на первом шаге, но на этот раз присоединить выходные данные к существующему заданию, чтобы увидеть упорядоченные выходные данные всех шагов в одном файле.

При планировании задания пользователям предоставляются широкие возможности. Вы можете выполнить задание один раз, как показано на экране 6, или составить расписание для повторного выполнения.

Экран 6. Планирование выполнения задания

Наконец, мы дошли до окна настройки параметров уведомления (см. экран 7). В нашем случае я хочу, чтобы мне (Тиму Форду) было послано уведомление о сбое задания через страницу, поскольку это важно. Если задание завершается (успешно или неудачно, неважно), я хочу получить по электронной почте сообщение о состоянии. Я также хочу сохранить в журнале Windows запись о состоянии задания, и, если оно выполнено успешно, удалить задание, так как оно предназначено лишь для разового выполнения.

Экран 7. Окно настройки параметров уведомления

Как уже подчеркивалось в начале статьи, агент SQL Server — инструмент с широкими возможностями. В следующей статье мы рассмотрим другие функции агента SQL Server, такие как:

• агент SQL Server Agent как система уведомления и предупреждения;
• агент SQL Server Agent и объекты оператора SQL;
• агент SQL Server и ведение журнала ошибок;
• агент SQL Server и прокси-серверы.

Simple way to create a SQL Server Job Using T-SQL

Sometimes we have a T-SQL process that we need to run that takes some time to run or we want to run it during idle time on the server. We could create a SQL Agent job manually, but is there any simple way to create a scheduled job?

Solution

This tip contains T-SQL code to create a SQL Agent job dynamically instead of having to use the SSMS GUI.

I am going to create a stored procedure named sp_add_job_quick that takes a few parameters to create the job. For my example, I will create a SQL Agent job that will call stored procedure sp_who and the job will be scheduled to run once at 4:00 PM.

Creating a Stored Procedure to create SQL Agent jobs

In this sample we are going to create a job dynamically using T-SQL Code:

USE msdb go CREATE procedure [dbo].[sp_add_job_quick] @job nvarchar(128), @mycommand nvarchar(max), @servername nvarchar(28), @startdate nvarchar(8), @starttime nvarchar(8) as --Add a job EXEC dbo.sp_add_job @job_name = @job ; --Add a job step named process step. This step runs the stored procedure EXEC sp_add_jobstep @job_name = @job, @step_name = N'process step', @subsystem = N'TSQL', @command = @mycommand --Schedule the job at a specified date and time exec sp_add_jobschedule @job_name = @job, @name = 'MySchedule', @freq_type=1, @active_start_date = @startdate, @active_start_time = @starttime -- Add the job to the SQL Server EXEC dbo.sp_add_jobserver @job_name = @job, @server_name = @servername

This is a stored procedure named sp_add_job_quick that calls 4 msdb stored procedures:

1. sp_add_job creates a new job
2. sp_add_jobstep adds a new step in the job
3. sp_add_jobschedule schedules a job for a specific date and time
4. sp_add_jobserver adds the job to a specific server

Let’s invoke the stored procedure in order to create the job:

exec dbo.sp_add_job_quick @job = 'myjob', -- The job name @mycommand = 'sp_who', -- The T-SQL command to run in the step @servername = 'serverName', -- SQL Server name. If running locally, you can use @servername=@@Servername @startdate = '20130829', -- The date August 29th, 2013 @starttime = '160000' -- The time, 16:00:00

If everything is OK, a job named myjob will be created with a step that runs the sp_who stored procedure that will run on August 29th at 4:00PM.

Explanation of the SQL Agent job creation code

Here I will walk through the code and what each step does.

The sp_add_job is a procedure in the msdb database that creates a job.

EXEC dbo.sp_add_job @job_name = @job

The sp_add_jobstep creates a job step in the job created. In this tip, the step name is process_step and the action is a TSQL command.

EXEC sp_add_jobstep @job_name = @job, @step_name = N'process step', @subsystem = N'TSQL', @command = @mycommand

In the declare section we are assigning to the @mycommand variable the stored procedure sp_who.

The following section let’s you create the schedule for the job in T-SQL. The schedule name is MySchedule. The frequency type is once (1). If you need to run the job daily the frequency type is 4 and weekly 8 . The active start time is 16:00:00 (4PM). The start date uses the date assigned to the startdate variable ‘20130823’.

exec sp_add_jobschedule @job_name = @job, @name = 'MySchedule', @freq_type=1, @active_start_date = @startdate, @active_start_time = @starttime

Some additional values for the sp_add_job

Some other parameters than can be useful for the sp_add_job stored procedure are the following:

Enabled

The @enabled parameter is used to set the status of the job. If enable is 0, the job is disabled. Otherwise, it is enabled.

Example

The following examples show how to enable (1) and disable (0) the jobs.

Description

It is an optional parameter used to describe the functionality of the job. It is a best practice to add a good explanation of what the job does. The description supports 512 characters.

Example

This parameter is used to describe the job itself.

• @description=N’This job is used for. ‘

Start_step_id

Sometimes, we do not start the job in step 1, but we start at another step. You can decide which one is the first step in the job with this parameter.

Example

The following example will start the job at step 3.

• @start_step_id =3

Category_id

You can use the category id instead of using the category name. The find the category ID and the category name, use the following query:

use msdb go select category_id, name from dbo.syscategories GO

Example

Delete_level

This parameter indicates if the job will be deleted. By default it is 0 which means that the job will not be deleted after execution. We can delete the job after a successful or a failure or even after completion. For this parameter we created an exclusive article for you: Automatic cleanup of SQL Server Agent scheduled jobs.

For more information about the sp_add_job, go to the next steps section.

Some additional parameters for the sp_add_job_step

Some other parameters than can be useful for the sp_add_job_step stored procedure are the following:

Subsystem

The @subsystem parameter is used to define the job action. Possible values are the CmdExec, Distribution, Snapshot, LogReader, Merge, ANALYSISQUERY, ANALYSISCOMMAND, SSIS, PowerShell, TSQL.

Examples

• @subsystem=N’CmdExec’
• @subsystem=N’PowerShell’
• @subsystem=N’ANALYSISCOMMAND’
• @subsystem=N’TSQL’

On_success_action

The @on_success_action parameter is used to define what to do if the step is executed successfully. You can quit the job reporting success (value equal to 1), quit reporting a failure (2), go to the next step (3) and finally, go to an specific success step id (4).

Examples

• @on_success_action=1
• @on_success_action=2
• @on_success_action=3

On_fail_action

The @on_fail_action parameter is used to define what to do if the step is executed with failure. You can quit the job reporting success (value equal to 1), quit reporting a failure (2), go to the next step (3) and finally, go to an specific success step id (4).

Examples

The following examples will show how to quit when the step success (1) and also will show how to quit on failure (2) and how to go to the next step (3):

• @on_success_action=1
• @on_success_action=2
• @on_success_action=3

Retry_attempts

The @retry_attempts parameter is used to define the number of retries in case the job fails.

Examples

The following examples show how to set the number of retry attempts to 1, 2 or 3:

• @retry_attempts=1
• @retry_attempts=2
• @retry_attempts=3

Retry_intervals

The @retry_intervals parameter is the number of minutes to wait between attempts.

Examples

The following example will show how to set the @retry_interval parameter into 15, 30 and 60 minutes.

• @retry_intervals=15
• @retry_intervals=30
• @retry_intervals=60

Command

The @command parameter depends on the subsystem, if the subsystem is TSQL, the command will be T-SQL statements, if the subsystem is CmdExec, the command will be the command line and so on.

Examples

The following example is an ANALYSISCOMMAND:

@command=N'     ProcessFull UseExisting   '

The following example copies a backup from the C drive to the D drive using PowerShell:

@command=N'Copy-Item "C:\adw.bak" -Destination "d:\"'

The following example copies a backup from the C drive to the D drive:

@command=N'copy c:\adw.bak d:'

Some additional parameters for the sp_add_schedule

Some other parameters than can be useful for the sp_add_schedule stored procedure are the following:

Freq_type

The @freq_type parameter is used to define how often will be the run the job. The number 1 is to run once, the number 4 to run daily, the number 8 to run weekly, 16 monthly, 64 when the SQL Agent service starts and 128 when the computer is idle.

Examples

The following example shows how to set the frequency to run weekly:

Active_start_date

The @active_start_date parameter is used to define the date that the job will start. The format is YYYYMMDD.

Examples

The following example shows how to set the active start date to 2021-10-14:

• @active_start_date=20211014.

Active_end_date

The @active_end_date is used to the date that the job will end. The format is YYYYMMDD.

Examples

The following example shows how to set the active end date to 2021-10-15:

• @active_end_date=20211015

Additional tables to find information about SQL Agent jobs

Here are some system tables in the msdb database that you can use to get job information. If you need to retrieve job information you may need them.

• dbo.sysjobactivity — shows the current information of the jobs
• dbo.sysjobhistory — shows the execution result of the jobs.
• dbo.sysjobs — shows the information of the jobs programmed.
• dbo.sysjobsshedules — shows the job schedule information like the next run date and time of the jobs.
• dbo.sysjobservers — shows the servers assigned to run jobs
• dbo.sysjobsteps — shows the job steps
• dbo.sysjobsteplogs — let you see the logs of the steps configured to display the output in a table

SQL Agent Job stored procedures

You also have these stored procedures in the msdb database to retrieve job information:

• sp_helpjob
• sp_helpjobactivity
• sp_helpjobcount
• sp_helpjobhistory
• sp_helpjobhistory_full
• sp_helpjobhistory_sem
• sp_helpjobhistory_summary
• sp_helpjobs_in_schedule
• sp_help_jobschedule
• sp_help_jobserver
• sp_help_jobstep
• sp_help_jobssteplog

I am not going to explain each system stored procedure, but in the next steps you can find links with an explanation for each.

If you want to modify and create your own procedures based on the Microsoft system stored procedures you can review the code using sp_helptext. In this example, we are reviewing sp_help_job. For example, to see the T-SQL code for sp_help_job code use this command:

sp_helptext '[dbo].[sp_help_job]'

The code is displayed here:

Next Steps

For more information about creating jobs with T-SQL refer to these links:

• Create a job
• Using sp_add_job
• Using sp_add_jobschedule
• SQL Server Agent Tables
• Using sp_help_job

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

Article Last Updated: 2021-10-15

Comments For This Article

Add Comment

Friday, December 18, 2020 — 5:09:16 PM — Spencer A Sullivan	Back To Top (87932)
Fantastic article and great explanations. Thank you!

Excelente articulo! me ayudo bastante, execlente explicación paso a paso.

I need a article with updation of every five seconds time interval.

Thank you very much for this article.

Very concise and straightforward with good example.

Nice Article, it really helped me.

Thank you very much for this post! It REALLY helped me.

i am trying to schedule a job in ql server 2005 for calling a stored procedure daily at a fixe time, at the time of adding the steps i am getting the message in window ‘after creating the steps that after executing the steps go to next step will goes to quit with success. is this intended behavoiur’ . while clicking on yes it gives exception

‘execute permission deny for sp_help_operator,msdb ‘.and it does not create the steps in job. could you provide me the solution .

thanks in advance

This is a usefull article. Thanks a lot.

The easiest way is to specify the database name in the command.

What is the parameter for the database setting if you don’t want it to always be «MASTER»?

Thanks for pointing that out. The tip has been updated to fix the typo you mentioned.

I was not aware of some of those system stored procedures, so this is very enlightening. Thank you.

Just a small nitpick, but is » The active start time is 16:00:00 (00:00:00).» a typo? Should it read (04:00:00) to translate the 24-hour clock to the more conventional 12-hour clock? Or am I just misunderstanding something?

Wednesday, May 23, 2018 — 9:49:56 AM — yetanis20	Back To Top (76010)