Запуск и проверка результата задания агента sql через сценарий оболочки

В основном мне нужно создать сценарий оболочки для:

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

Я планирую создать хранимую процедуру с таким параметром имени задания:

-- Start job
EXEC msdb.dbo.sp_start_job @job_name = @job_name

-- Wait for Job to finish
DECLARE @job_status INT
SELECT @job_status = current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=<server>;','exec msdb.dbo.sp_help_job @job_name = @job_name ')

WHILE @job_status <> 4
BEGIN
    WAITFOR DELAY '00:00:03'
    SELECT @job_status = current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=<server>;','exec msdb.dbo.sp_help_job @job_name = @job_name')
END

-- Get Result

SELECT TOP 1 @job_history_id = activity.job_history_id
    FROM msdb.dbo.sysjobs jobs
    INNER JOIN msdb.dbo.sysjobactivity activity ON activity.job_id = jobs.job_id
    WHERE jobs.name = @job_name
    ORDER BY activity.start_execution_date DESC

SELECT history.run_status
FROM msdb.dbo.sysjobhistory history
WHERE history.instance_id = @job_history_id

В идеале я мог бы запускать каждое задание агента SQL через этот store_proc, чтобы я мог очень легко вызывать этот store_proc через sqlcmd с именем задания в качестве параметра и сохранять вывод как результат задания.
Поскольку я не самый опыт работы с SQL Server, я обращаюсь к вам за помощью.

  1. Эффективен ли мой хранимый процесс? может ли кто-нибудь просветить меня на лучший способ сделать это?
  2. Должен ли этот хранимый процесс быть создан в базе данных msdb, и если да, то должен ли он быть сохранен как системный объект?
  3. Будет ли это публиковать проблему с миграциями или каким-либо управлением (например, специальные действия для переноса версий SQL/на разные серверы, чтобы сохранить этот хранимый процесс)?

Заранее спасибо за любую поддержку.


sql-server stored-procedures sqlcmd sql-job
person Joel Almeida    schedule 15.06.2020    source источник
comment
Запуск цикла для проверки завершения чего-либо — не лучшая идея, но ее можно использовать. Чего вы пытаетесь достичь здесь, то есть что происходит после завершения работы?   -  person Alex    schedule 16.06.2020
comment
это задание восстановит некоторые базы данных, после успешного запуска будут запущены тесты   -  person Joel Almeida    schedule 16.06.2020
comment
Почему бы не добавить тесты в качестве дополнительного этапа работы?   -  person Alex    schedule 19.06.2020
comment
на самом деле, тесты выполняются с использованием специально созданных фреймворков, и поэтому они должны управляться CI Server, а не SQL-сервером. основная идея здесь заключалась бы в том, чтобы дождаться задания восстановления и проверить, было ли оно успешным   -  person Joel Almeida    schedule 22.06.2020

Ответы (1)


arrow_upward
0
arrow_downward

Для тех, кто ищет ответ на этот вопрос, я пришел к хранимой процедуре, которая работает довольно хорошо:

CREATE PROCEDURE dbo.sp_sp_start_job_wait
(
@job_name SYSNAME,
@WaitTime DATETIME = '00:00:10', --check frequency
@JobCompletionStatus INT = null OUTPUT
)
AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @job_owner sysname

-- Create temp table with xp_sqlagent_enum_jobs structure
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)


SELECT @job_id = job_id FROM msdb.dbo.sysjobs
WHERE name = @job_name

SELECT @job_owner = SUSER_SNAME()

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id


-- Start the job if the job is not running
IF NOT EXISTS(SELECT TOP 1 * FROM #xp_results WHERE running = 1)
EXEC msdb.dbo.sp_start_job @job_name = @job_name

-- wait a couple of seconds to compute
WAITFOR DELAY '00:00:02'

DELETE FROM #xp_results
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

WHILE EXISTS(SELECT TOP 1 * FROM #xp_results WHERE running = 1)
BEGIN

WAITFOR DELAY @WaitTime

DELETE FROM #xp_results

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

END

SELECT top 1 @JobCompletionStatus = run_status
FROM msdb.dbo.sysjobhistory
WHERE job_id = @job_id
and step_id = 0
order by run_date desc, run_time desc

IF @JobCompletionStatus = 1
PRINT 'The job ran Successful'
ELSE IF @JobCompletionStatus = 3
PRINT 'The job is Cancelled'
ELSE
BEGIN
RAISERROR ('[ERROR]:%s job is either failed or not in good state. Please check',16, 1, @job_name) WITH LOG
END

RETURN @JobCompletionStatus

GO
person Joel Almeida    schedule 16.06.2020