T-SQL: Поиск по джобам

Для поиска джоба можно применять следующий скрипт:

select j.job_id, j.name, s.step_name, j.description
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobsteps s on j.job_id = s.job_id
where s.command like '%some_text%'

s.command - команда, которую выполняет шаг джоба;
j.name - название джоба;
s.step_name - название шага джоба;
j.description - описание джоба.

nolock при обращении к талице на линкованом сервере

Если обратиться к таблице на линкованом SQL-сервере с директивой nolock:

SELECT * FROM [192.168.0.1].Users.dbo.users (nolock)

то будет выдаваться ошибка:

Msg 4122, Level 16, State 1, Line 1
Remote table-valued function calls are not allowed.

Решается вот так:

SELECT * FROM [192.168.0.1].Users.dbo.users WITH (nolock)

Ответ нашел тут.

SSRS: Как рассылать отчеты по подписке НЕ по расписанию

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

Все относиться к SQL Server 2008 R2 Enterprise Edition, т.к. от версии к версии база SSRS может меняться. Я делал подписку управляемую данными и не уверен, что все нижеследующее подойдет к обычной подписке, не проверял.

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

Так вот, при создании расписания рассылки отчета, SSRS создает на сервере, где лежит его база, джоб. Называться он будет как-нибудь вот так: 2D45C6DB-559E-43CB-A019-F8F1484E17FF и он будет запускаться по расписанию, которое было указано в SSRS.

В джобе вызывается процедура ReportServer.dbo.AddEvent, которая кладет ID подписки в таблицу ReportServer.dbo.Event, из этой таблицы SSRS понимает, что пора отчет отсылать, после отсылки запись из таблицы ReportServer.dbo.Event удаляется. 

Исходя из вышесказанного, задача сводиться к тому, что надо добавить запись с ID подписки в таблицу ReportServer.dbo.Event в тот момент, когда пора отсылать отчет.

Шаг 1 - узнаем ID отчета (все селекты идут в БД ReportServer):

select ItemID from catalog where name = @name

Шаг 2 - узнаем ID подписки:

select SubscriptionID from Subscriptions where report_OID =@ItemID

 Шаг 3 - записываем данные в таблицу Event:

exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData=@SubscriptionID

В моем случае, в джобе, после шага, который процессит куб, я добавил шаг, который выполняет:

exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='fa502959-73cf-59a1-b018-8ddcdae2e01'

Надо иметь в виду, что если отчет удалить, а потом выложить заново, все ID поменяются . Если отчет выложить,  перезаписав его, то все ID останутся прежними.

И еще есть важный момент - надо сделать так, что бы расписание самого отчета не срабатывало, для чего в интерфейсе SSRS на странице создания расписания надо поставить Однократно.

T-SQL: Как проверить выполнение джоба

Бывают случаи, когда необходимо сделать так, что в джобе некоторые шаги при ошибочном выполнении не приводят к завершению джоба с ошибкой, а переходят на другой шаг джоба. У меня таким образом работает куб, который процессит OLAP-кубы. Что бы не следить каждый день за выполнением джоба, мы настроили SMS-рассылку. Для этого потребовалось разработать механизм проверки работы джоба. Т.е. задача - найти шаги джоба, которые завершились ошибкой, но при этом джоб продолжил работу.

Для начала нужно получить ID джоба:

select job_id from msdb..sysjobs where name = @job_name

Лог работы джобов лежит в таблице msdb..sysjobhistory. Нужно вытащить записи, которые относятся к последнему выполнению джоба. У меня джоб выполняется каждый день, поэтому просто беру сегодняшнее число. Но в таблице msdb..sysjobhistory дата начала выполнения шага храниться не в datetime, а в двух полях типа int: run_date и run_time, т.е. дата 2011-08-11 13:54:33 будет лежать в виде run_date = 20110811 и run_time = 135433.

Наверное с такими датами можно было бы работать и в интовых значениях, но мне как то привычнее в datatime. Для преобразования такого формата в datetime я написал две функции:

 -- Конвертация времени из формата HHMMSS типа int в секунды
create function [dbo].[fn_convert_intdt_to_sec] ( @ts int )
    returns int
as
begin
    if (@ts < 60) return @ts
     
    declare @sec int = @ts - (@ts/100)*100
    declare @min int = (@ts - (@ts/10000)*10000)/100
    declare @hr int = @ts/10000
     
    return @hr*3600 + @min*60 + @sec
end

-- Конвертация времени из формата даты в джобе в нормальный DateTime
create function [dbo].[fn_convert_job_date_to_datetime]( @date int, @time int )
returns DateTime
as
begin
    return dateadd(second, dbo.fn_convert_intdt_to_sec(@time), convert(datetime, cast(@date as varchar), 112))
end

Далее выводим список шагов:

select instance_id, step_id, step_name, run_status, dbo.fn_convert_job_date_to_datetime(run_date, run_time) run_dt
from msdb..sysjobhistory
where job_id = @job_id
and step_id <> 0 and dbo.fn_convert_job_date_to_datetime(run_date, run_time) >= @dt
order by step_id

step_id = 0 - это строка для всего джоба, поэтомы ее исключаем.

Тут (http://msdn.microsoft.com/en-us/library/ms174997.aspx) лежит описание таблицы  msdb..sysjobhistory.Статусы у шага могут быть такие:

0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled

 В проге, которая дергает данные, я проверяю, что у всех шагов статус = 1 , если нет, то шлю СМС с ошибкой.