Tracing SQL Agent SMO

ReadMe.md

Captured SMO queries against SQL Agent to try and figure out why tf I can read SQL Agent schedule data in RDS

Reference snippets for https://dba.stackexchange.com/q/347514/68127

formatted-smo.sql

declare
    @_msparam_0 nvarchar(4000),
    @_msparam_1 nvarchar(4000),
    @_msparam_2 nvarchar(4000),
    @_msparam_3 nvarchar(4000),
    @_msparam_4 nvarchar(4000),
    @_msparam_5 nvarchar(4000);

select
    @_msparam_0 = N'syspolicy_purge_history',
    @_msparam_1 = N'0',
    @_msparam_2 = N'syspolicy_purge_history_schedule',
    @_msparam_3 = N'8',
    @_msparam_4 = N'syspolicy_purge_history',
    @_msparam_5 = N'0';

create table #tmp_sp_help_jobschedule (
    schedule_id            int              null,
    schedule_name          nvarchar(128)    null,
    enabled                int              null,
    freq_type              int              null,
    freq_interval          int              null,
    freq_subday_type       int              null,
    freq_subday_interval   int              null,
    freq_relative_interval int              null,
    freq_recurrence_factor int              null,
    active_start_date      int              null,
    active_end_date        int              null,
    active_start_time      int              null,
    active_end_time        int              null,
    date_created           datetime         null,
    schedule_description   nvarchar(4000)   null,
    next_run_date          int              null,
    next_run_time          int              null,
    schedule_uid           uniqueidentifier null,
    job_count              int              null,
    job_id                 uniqueidentifier null
);

declare @job_id sysname;

declare crs cursor local fast_forward for(
    select sv.job_id as [JobID]
    from msdb.dbo.sysjobs_view as sv
    where (
        sv.name = @_msparam_0
        and sv.category_id = @_msparam_1
    ));

open crs;

fetch crs
into @job_id;

while @@fetch_status >= 0
begin
    insert into #tmp_sp_help_jobschedule (
        schedule_id,
        schedule_name,
        enabled,
        freq_type,
        freq_interval,
        freq_subday_type,
        freq_subday_interval,
        freq_relative_interval,
        freq_recurrence_factor,
        active_start_date,
        active_end_date,
        active_start_time,
        active_end_time,
        date_created,
        schedule_description,
        next_run_date,
        next_run_time,
        schedule_uid,
        job_count
    )
    exec msdb.dbo.sp_help_jobschedule @job_id = @job_id;

    update #tmp_sp_help_jobschedule
    set job_id = @job_id
    where job_id is null;

    fetch crs
    into @job_id;
end;

close crs;
deallocate crs;

select
    tshj.schedule_name as [Name],
    tshj.schedule_id as [ID],
    tshj.date_created as [DateCreated],
    cast(tshj.enabled as bit) as [IsEnabled],
    tshj.freq_type as [FrequencyTypes],
    tshj.freq_interval as [FrequencyInterval],
    tshj.freq_subday_type as [FrequencySubDayTypes],
    tshj.freq_subday_interval as [FrequencySubDayInterval],
    tshj.freq_relative_interval as [FrequencyRelativeIntervals],
    tshj.freq_recurrence_factor as [FrequencyRecurrenceFactor],
    null as [ActiveStartDate],
    0 as [ActiveStartTimeOfDay],
    0 as [ActiveEndTimeOfDay],
    null as [ActiveEndDate],
    tshj.job_count as [JobCount],
    tshj.schedule_uid as [ScheduleUid],
    tshj.active_start_date as [ActiveStartDateInt],
    tshj.active_end_date as [ActiveEndDateInt],
    tshj.active_start_time as [ActiveStartTimeOfDayInt],
    tshj.active_end_time as [ActiveEndTimeOfDayInt]
from msdb.dbo.sysjobs_view as sv
inner join #tmp_sp_help_jobschedule as tshj on tshj.job_id = sv.job_id
where (
    tshj.schedule_name = @_msparam_2
    and tshj.schedule_id = @_msparam_3
)
  and ((
          sv.name = @_msparam_4
     and  sv.category_id = @_msparam_5
      )
  );

drop table #tmp_sp_help_jobschedule;

get-smo.ps1

<#
.Description
    Engage SQL Profiler against localhost while running the below query to capture the 
    SQL texts above
#>

$SqlInstance = Connect-DbaInstance .

$AllJobs = $SqlInstance.JobServer.Jobs

$AllJobs[0].JobSchedules

raw-smo.sql

exec sp_executesql N'
create table #tmp_sp_help_jobschedule
(schedule_id int null, schedule_name nvarchar(128) null, enabled int null, freq_type int null, freq_interval int null, freq_subday_type int null, freq_subday_interval int null, freq_relative_interval int null, freq_recurrence_factor int null, active_start_date int null, active_end_date int null, active_start_time int null, active_end_time int null, date_created datetime null, schedule_description nvarchar(4000) null, next_run_date int null, next_run_time int null, schedule_uid uniqueidentifier null,  job_count int null, job_id uniqueidentifier null)
declare @job_id sysname
declare crs cursor local fast_forward
for ( SELECT
sv.job_id AS [JobID]
FROM
msdb.dbo.sysjobs_view AS sv
WHERE
(sv.name=@_msparam_0 and sv.category_id=@_msparam_1) ) 
open crs 
fetch crs into @job_id
while @@fetch_status >= 0 
begin 
	insert into #tmp_sp_help_jobschedule (schedule_id, schedule_name, enabled, freq_type, freq_interval, freq_subday_type, freq_subday_interval, freq_relative_interval, freq_recurrence_factor, active_start_date, active_end_date, active_start_time, active_end_time, date_created, schedule_description, next_run_date, next_run_time, schedule_uid, job_count) 
		exec msdb.dbo.sp_help_jobschedule @job_id = @job_id
	update #tmp_sp_help_jobschedule set job_id = @job_id where job_id is null
	fetch crs into @job_id
end 
close crs
deallocate crs


SELECT
tshj.schedule_name AS [Name],
tshj.schedule_id AS [ID],
tshj.date_created AS [DateCreated],
CAST(tshj.enabled AS bit) AS [IsEnabled],
tshj.freq_type AS [FrequencyTypes],
tshj.freq_interval AS [FrequencyInterval],
tshj.freq_subday_type AS [FrequencySubDayTypes],
tshj.freq_subday_interval AS [FrequencySubDayInterval],
tshj.freq_relative_interval AS [FrequencyRelativeIntervals],
tshj.freq_recurrence_factor AS [FrequencyRecurrenceFactor],
null AS [ActiveStartDate],
0 AS [ActiveStartTimeOfDay],
0 AS [ActiveEndTimeOfDay],
null AS [ActiveEndDate],
tshj.job_count AS [JobCount],
tshj.schedule_uid AS [ScheduleUid],
tshj.active_start_date AS [ActiveStartDateInt],
tshj.active_end_date AS [ActiveEndDateInt],
tshj.active_start_time AS [ActiveStartTimeOfDayInt],
tshj.active_end_time AS [ActiveEndTimeOfDayInt]
FROM
msdb.dbo.sysjobs_view AS sv
INNER JOIN #tmp_sp_help_jobschedule AS tshj ON tshj.job_id=sv.job_id
WHERE
(tshj.schedule_name=@_msparam_2 and tshj.schedule_id=@_msparam_3)and((sv.name=@_msparam_4 and sv.category_id=@_msparam_5))

            drop table #tmp_sp_help_jobschedule
        
',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'syspolicy_purge_history',@_msparam_1=N'0',@_msparam_2=N'syspolicy_purge_history_schedule',@_msparam_3=N'8',@_msparam_4=N'syspolicy_purge_history',@_msparam_5=N'0'

sp_help_jobschedule.sql

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO


CREATE   PROCEDURE sp_help_jobschedule
  @job_id              uniqueidentifier = null,
  @job_name            sysname          = null,
  @schedule_name       sysname          = null,
  @schedule_id         int              = null,
  @include_description bit              = 0 -- 1 if a schedule description is required (NOTE: It's expensive to generate the description)
as
begin
  declare @retval                 int
  declare @schedule_description   nvarchar(255)
  declare @name                   sysname
  declare @freq_type              int
  declare @freq_interval          int
  declare @freq_subday_type       int
  declare @freq_subday_interval   int
  declare @freq_relative_interval int
  declare @freq_recurrence_factor int
  declare @active_start_date      int
  declare @active_end_date        int
  declare @active_start_time      int
  declare @active_end_time        int
  declare @schedule_id_as_char    varchar(10)
  declare @job_count               int

  set nocount on

  -- Remove any leading/trailing spaces from parameters
  SELECT @schedule_name = LTRIM(RTRIM(@schedule_name))
  SELECT @job_count = 0

  -- Turn [nullable] empty string parameters into NULLs
  IF (@schedule_name = N'') SELECT @schedule_name = NULL

  -- The user must provide either:
  -- 1) job_id (or job_name) and (optionally) a schedule name
  -- or...
  -- 2) just schedule_id
  IF (@schedule_id IS NULL) AND
     (@job_id      IS NULL) AND
     (@job_name    IS NULL)
  BEGIN
    RAISERROR(14273, -1, -1)
    RETURN(1) -- Failure
  END

  IF (@schedule_id IS NOT NULL) AND ((@job_id        IS NOT NULL) OR
                                     (@job_name      IS NOT NULL) OR
                                     (@schedule_name IS NOT NULL))
  BEGIN
    RAISERROR(14273, -1, -1)
    RETURN(1) -- Failure
  END

  -- Check that the schedule (by ID) exists and it is only used by one job.
  -- Allowing this for backward compatibility with versions prior to V9
  IF (@schedule_id IS NOT NULL) AND
     (@job_id      IS NULL) AND
     (@job_name    IS NULL)
  BEGIN

    SELECT @job_count = COUNT(*)
    FROM msdb.dbo.sysjobschedules
    WHERE (schedule_id = @schedule_id)

    if(@job_count > 1)
    BEGIN
      SELECT @schedule_id_as_char = CONVERT(VARCHAR, @schedule_id)
      RAISERROR(14369, -1, -1, @schedule_id_as_char)
      RETURN(1) -- Failure
    END

    SELECT @job_id = job_id
    FROM msdb.dbo.sysjobschedules
    WHERE (schedule_id = @schedule_id)
    IF (@job_id IS NULL)
    BEGIN
      SELECT @schedule_id_as_char = CONVERT(VARCHAR, @schedule_id)
      RAISERROR(14262, -1, -1, '@schedule_id', @schedule_id_as_char)
      RETURN(1) -- Failure
    END
  END

  -- Check that we can uniquely identify the job
  IF (@job_id IS NOT NULL) OR (@job_name IS NOT NULL)
  BEGIN
    EXECUTE @retval = sp_verify_job_identifiers '@job_name',
                                                '@job_id',
                                                 @job_name OUTPUT,
                                                 @job_id   OUTPUT,
                                                'NO_TEST'
    IF (@retval <> 0)
      RETURN(1) -- Failure
  END

  IF (@schedule_id IS NOT NULL OR @schedule_name IS NOT NULL)
  BEGIN
    -- Check that we can uniquely identify the schedule
    EXECUTE @retval = msdb.dbo.sp_verify_schedule_identifiers @name_of_name_parameter = '@schedule_name',
                                                              @name_of_id_parameter   = '@schedule_id',
                                                              @schedule_name          = @schedule_name OUTPUT,
                                                              @schedule_id            = @schedule_id   OUTPUT,
                                                              @owner_sid              = NULL,
                                                              @orig_server_id         = NULL,
                                                              @job_id_filter          = @job_id
    IF (@retval <> 0)
      RETURN(1) -- Failure

  END

  -- Check that the schedule (by name) exists
  IF (@schedule_name IS NOT NULL)
  BEGIN
    IF (NOT EXISTS (SELECT *
                    FROM msdb.dbo.sysjobschedules AS js
                      JOIN msdb.dbo.sysschedules AS s
                        ON js.schedule_id = s.schedule_id
                    WHERE (js.job_id = @job_id)
                      AND (s.name = @schedule_name)))
    BEGIN
      RAISERROR(14262, -1, -1, '@schedule_name', @schedule_name)
      RETURN(1) -- Failure
    END
  END

  -- Get the schedule(s) into a temporary table
  SELECT s.schedule_id,
        'schedule_name' = name,
         enabled,
         freq_type,
         freq_interval,
         freq_subday_type,
         freq_subday_interval,
         freq_relative_interval,
         freq_recurrence_factor,
         active_start_date,
         active_end_date,
         active_start_time,
         active_end_time,
         date_created,
        'schedule_description' = FORMATMESSAGE(14549),
         js.next_run_date,
         js.next_run_time,
         s.schedule_uid
  INTO #temp_jobschedule
  FROM msdb.dbo.sysjobschedules AS js
    JOIN msdb.dbo.sysschedules AS s
      ON js.schedule_id = s.schedule_id
  WHERE ((@job_id IS NULL) OR (js.job_id = @job_id))
    AND ((@schedule_name IS NULL) OR (s.name = @schedule_name))
    AND ((@schedule_id IS NULL) OR (s.schedule_id = @schedule_id))

  IF (@include_description = 1)
  BEGIN
    -- For each schedule, generate the textual schedule description and update the temporary
    -- table with it
    IF (EXISTS (SELECT *
                FROM #temp_jobschedule))
    BEGIN
      WHILE (EXISTS (SELECT *
                     FROM #temp_jobschedule
                     WHERE schedule_description = FORMATMESSAGE(14549)))
      BEGIN
        SET ROWCOUNT 1
        SELECT @name                   = schedule_name,
               @freq_type              = freq_type,
               @freq_interval          = freq_interval,
               @freq_subday_type       = freq_subday_type,
               @freq_subday_interval   = freq_subday_interval,
               @freq_relative_interval = freq_relative_interval,
               @freq_recurrence_factor = freq_recurrence_factor,
               @active_start_date      = active_start_date,
               @active_end_date        = active_end_date,
               @active_start_time      = active_start_time,
               @active_end_time        = active_end_time
        FROM #temp_jobschedule
        WHERE (schedule_description = FORMATMESSAGE(14549))
        SET ROWCOUNT 0

        EXECUTE sp_get_schedule_description
          @freq_type,
          @freq_interval,
          @freq_subday_type,
          @freq_subday_interval,
          @freq_relative_interval,
          @freq_recurrence_factor,
          @active_start_date,
          @active_end_date,
          @active_start_time,
          @active_end_time,
          @schedule_description OUTPUT

        UPDATE #temp_jobschedule
        SET schedule_description = ISNULL(LTRIM(RTRIM(@schedule_description)), FORMATMESSAGE(14205))
        WHERE (schedule_name = @name)
      END -- While
    END
  END

  -- Return the result set, adding job count to it
  SELECT *, (SELECT COUNT(*) FROM sysjobschedules WHERE sysjobschedules.schedule_id = #temp_jobschedule.schedule_id) as 'job_count'
  FROM #temp_jobschedule
  ORDER BY schedule_id

  RETURN(@@error) -- 0 means success
END

GO