  CREATE TABLE sysdbmaintplans
  (
  plan_id                    UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED,
  plan_name                  sysname          NOT NULL,
  date_created               DATETIME         NOT NULL DEFAULT (GETDATE()),
  owner                      sysname          NOT NULL DEFAULT (ISNULL(NT_CLIENT(), SUSER_SNAME())),
  max_history_rows           INT              NOT NULL DEFAULT (0),
  remote_history_server      sysname          NOT NULL DEFAULT (''),
  max_remote_history_rows    INT              NOT NULL DEFAULT (0),
  user_defined_1             INT              NULL,
  user_defined_2             NVARCHAR(100)    NULL,
  user_defined_3             DATETIME         NULL,
  user_defined_4             UNIQUEIDENTIFIER NULL
  )
END
go

-- Add row for "plan 0"
IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysdbmaintplans
                WHERE (plan_id = CONVERT(UNIQUEIDENTIFIER, 0x00))))
  INSERT INTO sysdbmaintplans(plan_id, plan_name, owner) VALUES (0x00, N'All ad-hoc plans', N'sa')
go

/**************************************************************/
/* SYSDBMAINTPLAN_JOBS                                        */
/**************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sysdbmaintplan_jobs')
                  AND (type = 'U')))
BEGIN
  PRINT ''
  PRINT 'Creating table sysdbmaintplan_jobs...'

  CREATE TABLE sysdbmaintplan_jobs
  (
  plan_id UNIQUEIDENTIFIER NOT NULL UNIQUE CLUSTERED (plan_id, job_id)
                                    FOREIGN KEY REFERENCES msdb.dbo.sysdbmaintplans (plan_id),
  job_id  UNIQUEIDENTIFIER NOT NULL
  )
END
go

/**************************************************************/
/* SYSDBMAINTPLAN_DATABASES                                   */
/**************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sysdbmaintplan_databases')
                  AND (type = 'U')))
BEGIN
  PRINT ''
  PRINT 'Creating table sysdbmaintplan_databases...'

  CREATE TABLE sysdbmaintplan_databases
  (
  plan_id       UNIQUEIDENTIFIER NOT NULL UNIQUE CLUSTERED (plan_id, database_name)
                                          FOREIGN KEY REFERENCES msdb.dbo.sysdbmaintplans (plan_id),
  database_name sysname          NOT NULL
  )
END
go

/**************************************************************/
/* SYSDBMAINTPLAN_HISTORY                                     */
/**************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sysdbmaintplan_history')
                  AND (type = 'U')))
BEGIN
  PRINT ''
  PRINT 'Creating table sysdbmaintplan_history...'

  CREATE TABLE sysdbmaintplan_history
  (
  sequence_id    INT               NOT NULL IDENTITY UNIQUE NONCLUSTERED,
  plan_id        UNIQUEIDENTIFIER  NOT NULL DEFAULT('00000000-0000-0000-0000-000000000000'),
  plan_name      sysname           NOT NULL DEFAULT('All ad-hoc plans'),
  database_name  sysname           NULL,
  server_name    sysname           NOT NULL DEFAULT (CONVERT(sysname, ServerProperty('ServerName'))),
  activity       NVARCHAR(128)     NULL,
  succeeded      BIT               NOT NULL DEFAULT (1),
  end_time       DATETIME          NOT NULL DEFAULT (GETDATE()),
  duration       INT               NULL     DEFAULT (0),
  start_time     AS                DATEADD (ss, -duration, end_time),
  error_number   INT               NOT NULL DEFAULT (0),
  message        NVARCHAR(512)     NULL
  )

  CREATE CLUSTERED INDEX clust ON sysdbmaintplan_history(plan_id)
END
-- ALTER TABLE to correct default constraint 
ELSE
BEGIN
  DECLARE @t TABLE
  (
  constraint_type         NVARCHAR(146)  COLLATE database_default NULL,
  constraint_name         sysname        COLLATE database_default NULL,
  delete_action           NVARCHAR(20)   COLLATE database_default NULL,
  update_action           NVARCHAR(20)   COLLATE database_default NULL,
  status_enabled          NVARCHAR(20)   COLLATE database_default NULL,
  status_for_replication  NVARCHAR(20)   COLLATE database_default NULL,
  constraint_keys         NVARCHAR(2126) COLLATE database_default NULL
  )

  INSERT INTO @t EXEC sp_helpconstraint N'sysdbmaintplan_history', 'nomsg'

  DECLARE @constraint_name sysname
  DECLARE @sql NVARCHAR(4000)

  SELECT @constraint_name = constraint_name 
  FROM   @t 
  WHERE  constraint_type = N'DEFAULT on column server_name' 
  AND    constraint_keys = N'(@@servername)'

  -- default found
  IF (@constraint_name IS NOT NULL)
  BEGIN
    PRINT ''
    PRINT 'Alter sysdbmaintplan_history ...'
    SELECT @sql = N'ALTER TABLE sysdbmaintplan_history DROP CONSTRAINT ' + @constraint_name
    EXEC (@sql)

    ALTER TABLE sysdbmaintplan_history 
      ADD CONSTRAINT servername_default DEFAULT (CONVERT(sysname, ServerProperty('ServerName')))
      FOR server_name
  END
END
go

/**************************************************************/
/* SPs for the maintenance plans                              */
/**************************************************************/
/**************************************************************/
/* sp_clear_dbmaintplan_by_db                                 */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_clear_dbmaintplan_by_db...'
GO
IF (EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sp_clear_dbmaintplan_by_db') AND (type = 'P')))
  DROP PROCEDURE sp_clear_dbmaintplan_by_db
GO
CREATE PROCEDURE sp_clear_dbmaintplan_by_db
  @db_name sysname
AS
BEGIN
  DECLARE planid_cursor CURSOR
  FOR
  select plan_id from msdb.dbo.sysdbmaintplan_databases where database_name=@db_name
  OPEN planid_cursor
  declare @planid uniqueidentifier
  FETCH NEXT FROM planid_cursor INTO @planid
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
      delete from msdb.dbo.sysdbmaintplan_databases where plan_id=@planid AND database_name=@db_name
      if (NOT EXISTS(select * from msdb.dbo.sysdbmaintplan_databases where plan_id=@planid))
      BEGIN
        --delete the job
        DECLARE jobid_cursor CURSOR
        FOR
        select job_id from msdb.dbo.sysdbmaintplan_jobs where plan_id=@planid
        OPEN jobid_cursor
        DECLARE @jobid uniqueidentifier
        FETCH NEXT FROM jobid_cursor INTO @jobid
        WHILE (@@FETCH_STATUS <> -1)
        BEGIN
          if (@@FETCH_STATUS <> -2)
          BEGIN
            execute msdb.dbo.sp_delete_job @jobid
          END
          FETCH NEXT FROM jobid_cursor into @jobid
        END
        CLOSE jobid_cursor
        DEALLOCATE jobid_cursor
        --delete the history
        delete from msdb.dbo.sysdbmaintplan_history where plan_id=@planid
        --delete the plan
        delete from msdb.dbo.sysdbmaintplans where plan_id=@planid
      END
    END
    FETCH NEXT FROM planid_cursor INTO @planid
  END
  CLOSE planid_cursor
  DEALLOCATE planid_cursor
END
GO

/**************************************************************/
/* sp_add_maintenance_plan                                    */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_add_maintenance_plan...'
GO
IF (EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sp_add_maintenance_plan') AND (type = 'P')))
  DROP PROCEDURE sp_add_maintenance_plan
GO
CREATE PROCEDURE sp_add_maintenance_plan
  @plan_name varchar(128),
  @plan_id   UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
  IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysdbmaintplans
                WHERE plan_name=@plan_name))
    BEGIN
      SELECT @plan_id=NEWID()
      INSERT INTO msdb.dbo.sysdbmaintplans (plan_id, plan_name) VALUES (@plan_id, @plan_name)
    END
  ELSE
    BEGIN
      RAISERROR(14261,-1,-1,'@plan_name',@plan_name)
      RETURN(1) -- failure
    END
END
GO

/**************************************************************/
/* sp_delete_maintenance_plan                                 */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_delete_maintenance_plan...'
GO
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_delete_maintenance_plan')
              AND (type = 'P')))
  DROP PROCEDURE sp_delete_maintenance_plan
GO
CREATE PROCEDURE sp_delete_maintenance_plan
  @plan_id UNIQUEIDENTIFIER
AS
BEGIN
  /*check if the plan_id is valid*/
  IF (NOT EXISTS(SELECT *
                 FROM sysdbmaintplans
                 WHERE plan_id=@plan_id))
  BEGIN
    DECLARE @syserr VARCHAR(100)
    SELECT @syserr=CONVERT(VARCHAR(100),@plan_id)
    RAISERROR(14262,-1,-1,'@plan_id',@syserr)
    RETURN(1)
  END
  /* clean the related records in sysdbmaintplan_database */
  DELETE FROM msdb.dbo.sysdbmaintplan_databases
  WHERE plan_id=@plan_id
  /* clean the related records in sysdbmaintplan_jobs*/
  DELETE FROM msdb.dbo.sysdbmaintplan_jobs
  WHERE plan_id=@plan_id
  /* clean sysdbmaintplans */
  DELETE FROM msdb.dbo.sysdbmaintplans
  WHERE  plan_id= @plan_id
END
GO

/**************************************************************/
/* sp_add_maintenance_plan_db                                 */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_add_maintenance_plan_db...'
GO
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_add_maintenance_plan_db')
              AND (type = 'P')))
  DROP PROCEDURE sp_add_maintenance_plan_db
GO
CREATE PROCEDURE sp_add_maintenance_plan_db
  @plan_id UNIQUEIDENTIFIER,
  @db_name sysname
AS
BEGIN
  DECLARE @syserr VARCHAR(100)
  /*check if the plan_id is valid */
  IF (NOT EXISTS (SELECT plan_id
              FROM  msdb.dbo.sysdbmaintplans
              WHERE plan_id=@plan_id))
  BEGIN
    SELECT @syserr=CONVERT(VARCHAR(100),@plan_id)
    RAISERROR(14262,-1,-1,'@plan_id',@syserr)
    RETURN(1)
  END
  /*check if the database name is valid */
  IF (NOT EXISTS (SELECT name
              FROM master.dbo.sysdatabases
              WHERE name=@db_name))
   BEGIN
    RAISERROR(14262,-1,-1,'@db_name',@db_name)
    RETURN(1)
  END
  /*check if the (plan_id, database) pair already exists*/
  IF (EXISTS (SELECT *
              FROM sysdbmaintplan_databases
              WHERE plan_id=@plan_id AND database_name=@db_name))
  BEGIN
    SELECT @syserr=CONVERT(VARCHAR(100),@plan_id)+' + '+@db_name
    RAISERROR(14261,-1,-1,'@plan_id+@db_name',@syserr)
    RETURN(1)
  END
  INSERT INTO msdb.dbo.sysdbmaintplan_databases (plan_id,database_name) VALUES (@plan_id, @db_name)
END
GO

/**************************************************************/
/* sp_delete_maintenance_plan_db                              */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_delete_maintenance_plan_db...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_delete_maintenance_plan_db')
              AND (type = 'P')))
  DROP PROCEDURE sp_delete_maintenance_plan_db
go
CREATE PROCEDURE sp_delete_maintenance_plan_db
  @plan_id uniqueidentifier,
  @db_name sysname
AS
BEGIN
  /*check if the (plan_id, db_name) exists in the table*/
  IF (NOT EXISTS(SELECT *
                 FROM msdb.dbo.sysdbmaintplan_databases
                 WHERE @plan_id=plan_id AND @db_name=database_name))
  BEGIN
    DECLARE @syserr VARCHAR(300)
    SELECT @syserr=CONVERT(VARCHAR(100),@plan_id)+' + '+@db_name
    RAISERROR(14262,-1,-1,'@plan_id+@db_name',@syserr)
    RETURN(1)
  END
  /*delete the pair*/
  DELETE FROM msdb.dbo.sysdbmaintplan_databases
  WHERE plan_id=@plan_id AND database_name=@db_name
END
GO

/**************************************************************/
/* sp_add_maintenance_plan_job                                */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_add_maintenance_plan_job...'
GO
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_add_maintenance_plan_job')
              AND (type = 'P')))
  DROP PROCEDURE sp_add_maintenance_plan_job
GO
CREATE PROCEDURE sp_add_maintenance_plan_job
  @plan_id UNIQUEIDENTIFIER,
  @job_id  UNIQUEIDENTIFIER
AS
BEGIN
  DECLARE @syserr varchar(100)
  /*check if the @plan_id is valid*/
  IF (NOT EXISTS(SELECT plan_id
                 FROM msdb.dbo.sysdbmaintplans
                 WHERE plan_id=@plan_id))
  BEGIN
    SELECT @syserr=CONVERT(VARCHAR(100),@plan_id)
    RAISERROR(14262,-1,-1,'@plan_id',@syserr)
    RETURN(1)
  END
  /*check if the @job_id is valid*/
  IF (NOT EXISTS(SELECT job_id
                 FROM msdb.dbo.sysjobs
                 WHERE job_id=@job_id))
  BEGIN
    SELECT @syserr=CONVERT(VARCHAR(100),@job_id)
    RAISERROR(14262,-1,-1,'@job_id',@syserr)
    RETURN(1)
  END
  /*check if the job has at least one step calling xp_sqlmaint*/
  DECLARE @maxind INT
  SELECT @maxind=(SELECT MAX(CHARINDEX('xp_sqlmaint', command))
                FROM  msdb.dbo.sysjobsteps
                WHERE @job_id=job_id)
  IF (@maxind<=0)
  BEGIN
    /*print N'Warning: The job is not for maitenance plan.' -- will add the new sysmessage here*/
    SELECT @syserr=CONVERT(VARCHAR(100),@job_id)
    RAISERROR(14199,-1,-1,@syserr)
    RETURN(1)
  END
  INSERT INTO msdb.dbo.sysdbmaintplan_jobs(plan_id,job_id) VALUES (@plan_id, @job_id) --don't have to check duplicate here
END
GO

/**************************************************************/
/* sp_delete_maintenance_plan_job                             */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_delete_maintenance_plan_job...'
GO
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_delete_maintenance_plan_job')
              AND (type = 'P')))
  DROP PROCEDURE sp_delete_maintenance_plan_job
GO
CREATE PROCEDURE sp_delete_maintenance_plan_job
  @plan_id uniqueidentifier,
  @job_id  uniqueidentifier
AS
BEGIN
  /*check if the (plan_id, job_id) exists*/
  IF (NOT EXISTS(SELECT *
                 FROM sysdbmaintplan_jobs
                 WHERE @plan_id=plan_id AND @job_id=job_id))
  BEGIN
    DECLARE @syserr VARCHAR(300)
    SELECT @syserr=CONVERT(VARCHAR(100),@plan_id)+' + '+CONVERT(VARCHAR(100),@job_id)
    RAISERROR(14262,-1,-1,'@plan_id+@job_id',@syserr)
    RETURN(1)
  END
  DELETE FROM msdb.dbo.sysdbmaintplan_jobs
  WHERE plan_id=@plan_id AND job_id=@job_id
END
GO

/**************************************************************/
/* sp_help_maintenance_plan                                   */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_help_maintenance_plan...'
GO
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_help_maintenance_plan')
              AND (type = 'P')))
  DROP PROCEDURE sp_help_maintenance_plan
GO
CREATE PROCEDURE sp_help_maintenance_plan
  @plan_id UNIQUEIDENTIFIER = NULL
AS
BEGIN
  IF (@plan_id IS NOT NULL)
    BEGIN
      /*return the information about the plan itself*/
      SELECT *
      FROM msdb.dbo.sysdbmaintplans
      WHERE plan_id=@plan_id
      /*return the information about databases this plan defined on*/
      SELECT database_name
      FROM msdb.dbo.sysdbmaintplan_databases
      WHERE plan_id=@plan_id
      /*return the information about the jobs that relating to the plan*/
      SELECT job_id
      FROM msdb.dbo.sysdbmaintplan_jobs
      WHERE plan_id=@plan_id
    END
  ELSE
    BEGIN
      SELECT *
      FROM msdb.dbo.sysdbmaintplans
    END
END
GO

/**************************************************************/
/*                                                            */
/* B A C K U P  H I S T O R Y                                 */
/*                                                            */
/**************************************************************/
/**************************************************************/
/* sp_delete_database_backuphistory                           */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_delete_database_backuphistory...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_delete_database_backuphistory')
              AND (type = 'P')))
  DROP PROCEDURE sp_delete_database_backuphistory
go
CREATE   PROCEDURE sp_delete_database_backuphistory
  @database_name sysname
AS
BEGIN
  SET NOCOUNT ON

  DECLARE @backup_set_id TABLE      (backup_set_id INT)
  DECLARE @media_set_id TABLE       (media_set_id INT)
  DECLARE @restore_history_id TABLE (restore_history_id INT)

  INSERT INTO @backup_set_id (backup_set_id)
  SELECT DISTINCT backup_set_id
  FROM msdb.dbo.backupset
  WHERE database_name = @database_name

  INSERT INTO @media_set_id (media_set_id)
  SELECT DISTINCT media_set_id
  FROM msdb.dbo.backupset
  WHERE database_name = @database_name

  INSERT INTO @restore_history_id (restore_history_id)
  SELECT DISTINCT restore_history_id
  FROM msdb.dbo.restorehistory
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)

  BEGIN TRANSACTION

  DELETE FROM msdb.dbo.backupfile
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.backupfilegroup
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.restorefile
  WHERE restore_history_id IN (SELECT restore_history_id
                               FROM @restore_history_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.restorefilegroup
  WHERE restore_history_id IN (SELECT restore_history_id
                               FROM @restore_history_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.restorehistory
  WHERE restore_history_id IN (SELECT restore_history_id
                               FROM @restore_history_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.backupset
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE msdb.dbo.backupmediafamily
  FROM msdb.dbo.backupmediafamily bmf
  WHERE bmf.media_set_id IN (SELECT media_set_id
                             FROM @media_set_id)
    AND ((SELECT COUNT(*)
          FROM msdb.dbo.backupset
          WHERE media_set_id = bmf.media_set_id) = 0)
  IF (@@error > 0)
    GOTO Quit

  DELETE msdb.dbo.backupmediaset
  FROM msdb.dbo.backupmediaset bms
  WHERE bms.media_set_id IN (SELECT media_set_id
                             FROM @media_set_id)
    AND ((SELECT COUNT(*)
          FROM msdb.dbo.backupset
          WHERE media_set_id = bms.media_set_id) = 0)
  IF (@@error > 0)
    GOTO Quit

  COMMIT TRANSACTION
  RETURN

Quit:
  ROLLBACK TRANSACTION
END
go

/**************************************************************/
/* SP_DELETE_BACKUPHISTORY                                    */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_delete_backuphistory...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_delete_backuphistory')
              AND (type = 'P')))
  DROP PROCEDURE sp_delete_backuphistory
go
CREATE   PROCEDURE sp_delete_backuphistory
  @oldest_date datetime
AS
BEGIN
  SET NOCOUNT ON

  DECLARE @backup_set_id TABLE      (backup_set_id INT)
  DECLARE @media_set_id TABLE       (media_set_id INT)
  DECLARE @restore_history_id TABLE (restore_history_id INT)

  INSERT INTO @backup_set_id (backup_set_id)
  SELECT DISTINCT backup_set_id
  FROM msdb.dbo.backupset
  WHERE backup_finish_date < @oldest_date

  INSERT INTO @media_set_id (media_set_id)
  SELECT DISTINCT media_set_id
  FROM msdb.dbo.backupset
  WHERE backup_finish_date < @oldest_date

  INSERT INTO @restore_history_id (restore_history_id)
  SELECT DISTINCT restore_history_id
  FROM msdb.dbo.restorehistory
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)

  BEGIN TRANSACTION

  DELETE FROM msdb.dbo.backupfile
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.backupfilegroup
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.restorefile
  WHERE restore_history_id IN (SELECT restore_history_id
                               FROM @restore_history_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.restorefilegroup
  WHERE restore_history_id IN (SELECT restore_history_id
                               FROM @restore_history_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.restorehistory
  WHERE restore_history_id IN (SELECT restore_history_id
                               FROM @restore_history_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.backupset
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE msdb.dbo.backupmediafamily
  FROM msdb.dbo.backupmediafamily bmf
  WHERE bmf.media_set_id IN (SELECT media_set_id
                             FROM @media_set_id)
    AND ((SELECT COUNT(*)
          FROM msdb.dbo.backupset
          WHERE media_set_id = bmf.media_set_id) = 0)
  IF (@@error > 0)
    GOTO Quit

  DELETE msdb.dbo.backupmediaset
  FROM msdb.dbo.backupmediaset bms
  WHERE bms.media_set_id IN (SELECT media_set_id
                             FROM @media_set_id)
    AND ((SELECT COUNT(*)
          FROM msdb.dbo.backupset
          WHERE media_set_id = bms.media_set_id) = 0)
  IF (@@error > 0)
    GOTO Quit

  COMMIT TRANSACTION
  RETURN

Quit:
  ROLLBACK TRANSACTION

END
go




/**********************************************************************/
/* TABLE : log_shipping_primaries                                     */
/* Populated on the monitor server                                    */
/*                                                                    */
/**********************************************************************/

IF (NOT EXISTS (SELECT *
            FROM INFORMATION_SCHEMA.TABLES
            WHERE (TABLE_NAME = N'log_shipping_primaries')))
BEGIN
 PRINT ''
 PRINT 'Creating table log_shipping_primaries...'
 CREATE TABLE log_shipping_primaries
 (
  primary_id                   INT IDENTITY     NOT NULL PRIMARY KEY,
  primary_server_name          sysname          NOT NULL,
  primary_database_name        sysname          NOT NULL,
  maintenance_plan_id          UNIQUEIDENTIFIER NULL,
  backup_threshold             INT              NOT NULL,
  threshold_alert              INT              NOT NULL,
  threshold_alert_enabled      BIT              NOT NULL, /* 1 = enabled, 0 = disabled */
  last_backup_filename         NVARCHAR(500)    NULL,
  last_updated                 DATETIME         NULL,
  planned_outage_start_time    INT              NOT NULL,
  planned_outage_end_time      INT              NOT NULL,
  planned_outage_weekday_mask  INT              NOT NULL,
  source_directory             NVARCHAR(500)    NULL
 )
END
ELSE 
BEGIN
  IF (NOT EXISTS (SELECT * 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_NAME = N'log_shipping_primaries')
      AND (COLUMN_NAME = N'source_directory')))

  BEGIN
    PRINT ''
    PRINT 'Adding columns to table log_shipping_primaries...'

    ALTER TABLE log_shipping_primaries
     ADD source_directory NVARCHAR(500) NULL

  END
END 
go

/**********************************************************************/
/* TABLE : log_shipping_secondaries                                   */
/* Populated on the monitor server                                    */
/*                                                                    */
/**********************************************************************/

IF (NOT EXISTS (SELECT *
            FROM INFORMATION_SCHEMA.TABLES
            WHERE (TABLE_NAME = N'log_shipping_secondaries')))
BEGIN
 PRINT ''
 PRINT 'Creating table log_shipping_secondaries...'
 CREATE TABLE log_shipping_secondaries
 (
  primary_id                   INT                FOREIGN KEY REFERENCES log_shipping_primaries (primary_id),
  secondary_server_name        sysname,
  secondary_database_name      sysname,
  last_copied_filename         NVARCHAR(500),
  last_loaded_filename         NVARCHAR(500),
  last_copied_last_updated     DATETIME,
  last_loaded_last_updated     DATETIME,
  secondary_plan_id            UNIQUEIDENTIFIER,
  copy_enabled                 BIT,
  load_enabled                 BIT,              /* 1 = load enabled, 0 = load disabled */
  out_of_sync_threshold        INT,
  threshold_alert              INT,
  threshold_alert_enabled      BIT,              /*1 = enabled, 0 = disabled */
  planned_outage_start_time    INT,
  planned_outage_end_time      INT,
  planned_outage_weekday_mask  INT,
  allow_role_change            BIT DEFAULT (0)
 )
END
ELSE 
BEGIN
  IF (NOT EXISTS (SELECT * 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_NAME = N'log_shipping_secondaries')
      AND (COLUMN_NAME = N'allow_role_change')))

  BEGIN
    PRINT ''
    PRINT 'Adding columns to table log_shipping_secondaries...'

    ALTER TABLE log_shipping_secondaries
     ADD allow_role_change BIT DEFAULT (0)

  END
END 
go

/**************************************************************/
/* sp_add_log_shipping_monitor_jobs                           */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_add_log_shipping_monitor_jobs...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_add_log_shipping_monitor_jobs' AND type = N'P')  )
  drop procedure sp_add_log_shipping_monitor_jobs
go
CREATE PROCEDURE sp_add_log_shipping_monitor_jobs AS 
BEGIN
  SET NOCOUNT ON
  BEGIN TRANSACTION
  DECLARE @rv INT
  DECLARE @backup_job_name sysname
  SET @backup_job_name = N'Log Shipping Alert Job - Backup'
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name = @backup_job_name))
  BEGIN
    EXECUTE @rv = msdb.dbo.sp_add_job @job_name = N'Log Shipping Alert Job - Backup'

    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error 

    EXECUTE @rv = msdb.dbo.sp_add_jobstep 
      @job_name = N'Log Shipping Alert Job - Backup', 
      @step_id = 1, 
      @step_name = N'Log Shipping Alert - Backup', 
      @command = N'EXECUTE msdb.dbo.sp_log_shipping_monitor_backup',
      @on_fail_action = 2, 
      @flags = 4, 
      @subsystem = N'TSQL', 
      @on_success_step_id = 0, 
      @on_success_action = 1, 
      @on_fail_step_id = 0
    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error 

   EXECUTE @rv = msdb.dbo.sp_add_jobschedule 
      @job_name = @backup_job_name, 
      @freq_type = 4, 
      @freq_interval = 1, 
      @freq_subday_type = 0x4, 
      @freq_subday_interval = 1, -- run every minute
      @freq_relative_interval = 0, 
      @name = @backup_job_name
    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error

   EXECUTE @rv = msdb.dbo.sp_add_jobserver @job_name = @backup_job_name, @server_name = NULL
    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error
  END

  DECLARE @restore_job_name sysname
  SET @restore_job_name = 'Log Shipping Alert Job - Restore'
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name = @restore_job_name))
  BEGIN
    EXECUTE @rv = msdb.dbo.sp_add_job @job_name = @restore_job_name

    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error 

    EXECUTE @rv = msdb.dbo.sp_add_jobstep 
      @job_name = @restore_job_name, 
      @step_id = 1, 
      @step_name = @restore_job_name, 
      @command = N'EXECUTE msdb.dbo.sp_log_shipping_monitor_restore',
      @on_fail_action = 2, 
      @flags = 4, 
      @subsystem = N'TSQL', 
      @on_success_step_id = 0, 
      @on_success_action = 1, 
      @on_fail_step_id = 0
    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error 

    EXECUTE @rv = msdb.dbo.sp_add_jobschedule 
      @job_name = @restore_job_name, 
      @freq_type = 4, 
      @freq_interval = 1, 
      @freq_subday_type = 0x4, 
      @freq_subday_interval = 1, -- run every minute
      @freq_relative_interval = 0, 
      @name = @restore_job_name
    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error

    EXECUTE @rv = msdb.dbo.sp_add_jobserver @job_name = @restore_job_name, @server_name = NULL
    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error
  END
  COMMIT TRANSACTION
  RETURN

rollback_quit:
  ROLLBACK TRANSACTION
END
go

/**************************************************************/
/* sp_add_log_shipping_primary                                */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_add_log_shipping_primary...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_add_log_shipping_primary' AND type = N'P'))
  drop procedure sp_add_log_shipping_primary
go
CREATE PROCEDURE sp_add_log_shipping_primary
  @primary_server_name         sysname,
  @primary_database_name       sysname,
  @maintenance_plan_id         UNIQUEIDENTIFIER = NULL,
  @backup_threshold            INT              = 60,
  @threshold_alert             INT              = 14420,
  @threshold_alert_enabled     BIT              = 1,
  @planned_outage_start_time   INT              = 0,
  @planned_outage_end_time     INT              = 0,
  @planned_outage_weekday_mask INT              = 0,
  @primary_id              INT = NULL OUTPUT       
AS
BEGIN
  SET NOCOUNT ON
  IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name)
  BEGIN  
    DECLARE @pair_name NVARCHAR 
   SELECT @pair_name = @primary_server_name + N'.' + @primary_database_name
   RAISERROR (14261,16,1, N'primary_server_name.primary_database_name', @pair_name)
    RETURN (1) -- error
  END
  INSERT INTO msdb.dbo.log_shipping_primaries (
    primary_server_name,
    primary_database_name,
    maintenance_plan_id,
    backup_threshold,
    threshold_alert,
    threshold_alert_enabled,
    last_backup_filename,
    last_updated,
    planned_outage_start_time,
    planned_outage_end_time,
    planned_outage_weekday_mask,
    source_directory)  
  VALUES (@primary_server_name,  
    @primary_database_name, 
    @maintenance_plan_id, 
    @backup_threshold,
    @threshold_alert,
    @threshold_alert_enabled,
    N'first_file_000000000000.trn',
    GETDATE (),
    @planned_outage_start_time,
    @planned_outage_end_time,
    @planned_outage_weekday_mask,
    NULL)

  SELECT @primary_id = @@IDENTITY

  EXECUTE msdb.dbo.sp_add_log_shipping_monitor_jobs
END
go

/**************************************************************/
/* sp_add_log_shipping_secondary                              */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_add_log_shipping_secondary...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_add_log_shipping_secondary' AND type = N'P'))
  drop procedure sp_add_log_shipping_secondary
go
CREATE PROCEDURE sp_add_log_shipping_secondary
  @primary_id                  INT,
  @secondary_server_name       sysname,
  @secondary_database_name     sysname,
  @secondary_plan_id           UNIQUEIDENTIFIER,
  @copy_enabled                BIT              = 1,
  @load_enabled                BIT              = 1,
  @out_of_sync_threshold       INT              = 60,
  @threshold_alert             INT              = 14421,
  @threshold_alert_enabled     BIT              = 1,
  @planned_outage_start_time   INT              = 0,
  @planned_outage_end_time     INT              = 0,
  @planned_outage_weekday_mask INT              = 0,
  @allow_role_change           BIT              = 0 
AS
BEGIN
  SET NOCOUNT ON
  IF NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries where primary_id = @primary_id)
  BEGIN
    RAISERROR (14262, 16, 1, N'primary_id', N'msdb.dbo.log_shipping_primaries')
    RETURN(1)
  END

  INSERT INTO msdb.dbo.log_shipping_secondaries (
    primary_id,
    secondary_server_name,
    secondary_database_name,
    last_copied_filename,
    last_loaded_filename,
    last_copied_last_updated,
    last_loaded_last_updated,
    secondary_plan_id,
    copy_enabled,
    load_enabled,
    out_of_sync_threshold,
    threshold_alert,
    threshold_alert_enabled,
    planned_outage_start_time,
    planned_outage_end_time,
    planned_outage_weekday_mask,
    allow_role_change)
   VALUES (@primary_id,
    @secondary_server_name,
    @secondary_database_name,
    N'first_file_000000000000.trn',
    N'first_file_000000000000.trn',
    GETDATE (),
    GETDATE (),
    @secondary_plan_id,
    @copy_enabled,
    @load_enabled,
    @out_of_sync_threshold,
    @threshold_alert,
    @threshold_alert_enabled,
    @planned_outage_start_time,
    @planned_outage_end_time,
    @planned_outage_weekday_mask,
    @allow_role_change)
END
go

/**************************************************************/
/* sp_delete_log_shipping_monitor_jobs                        */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_delete_log_shipping_monitor_jobs...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_delete_log_shipping_monitor_jobs' AND type = N'P')  )
  drop procedure sp_delete_log_shipping_monitor_jobs
go
CREATE PROCEDURE sp_delete_log_shipping_monitor_jobs AS
BEGIN
  DECLARE @backup_job_name sysname
  SET NOCOUNT ON
  SET @backup_job_name = N'Log Shipping Alert Job - Backup'
  IF (EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name = @backup_job_name))
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Log Shipping Alert Job - Backup'

  DECLARE @restore_job_name sysname
  SET @restore_job_name = 'Log Shipping Alert Job - Restore'
  IF (EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name = @restore_job_name))
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Log Shipping Alert Job - Restore'
END
go

/**************************************************************/
/* sp_delete_log_shipping_primary                             */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_delete_log_shipping_primary...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_delete_log_shipping_primary' AND type = N'P')  )
  drop procedure sp_delete_log_shipping_primary
go
CREATE PROCEDURE sp_delete_log_shipping_primary 
  @primary_server_name sysname,
  @primary_database_name sysname,
  @delete_secondaries BIT = 0
AS BEGIN
  DECLARE @primary_id INT

  SET NOCOUNT ON

  SELECT @primary_id = primary_id 
    FROM msdb.dbo.log_shipping_primaries 
    WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name
  IF (@primary_id IS NULL)
    RETURN (0)

  BEGIN TRANSACTION
  IF (EXISTS (SELECT * FROM msdb.dbo.log_shipping_secondaries WHERE primary_id = @primary_id))
  BEGIN
    IF (@delete_secondaries = 0)
    BEGIN
      RAISERROR (14429,-1,-1)
      goto rollback_quit
    END
    DELETE FROM msdb.dbo.log_shipping_secondaries WHERE primary_id = @primary_id
    IF (@@ERROR <> 0)
      GOTO rollback_quit
  END
  DELETE FROM msdb.dbo.log_shipping_primaries WHERE primary_id = @primary_id
  IF (@@ERROR <> 0)
    GOTO rollback_quit

  COMMIT TRANSACTION
  DECLARE @i INT
  SELECT @i = COUNT(*) FROM msdb.dbo.log_shipping_primaries
  IF (@i=0)
    EXECUTE msdb.dbo.sp_delete_log_shipping_monitor_jobs
  RETURN (0)

rollback_quit:
  ROLLBACK TRANSACTION
  RETURN(1) -- error
END
go

/**************************************************************/
/* sp_delete_log_shipping_secondary                           */
/**************************************************************/
PRINT ''
PRINT 'Creating sp_delete_log_shipping_secondary...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_delete_log_shipping_secondary' AND type = N'P')  )
  drop procedure sp_delete_log_shipping_secondary
go
CREATE PROCEDURE sp_delete_log_shipping_secondary 
  @secondary_server_name   sysname,
  @secondary_database_name sysname
AS BEGIN
  SET NOCOUNT ON
  DELETE FROM msdb.dbo.log_shipping_secondaries WHERE 
    secondary_server_name   = @secondary_server_name AND
    secondary_database_name = @secondary_database_name
END
go

/**************************************************************/
/* sp_log_shipping_in_sync                                    */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_shipping_in_sync...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_log_shipping_in_sync' AND type = N'P')  )
  drop procedure sp_log_shipping_in_sync
go
CREATE PROCEDURE sp_log_shipping_in_sync
  @last_updated        DATETIME,
  @compare_with        DATETIME,
  @threshold           INT,
  @outage_start_time   INT,
  @outage_end_time     INT,
  @outage_weekday_mask INT,
  @enabled             BIT = 1,
  @delta               INT = NULL OUTPUT
AS BEGIN
  SET NOCOUNT ON
  DECLARE @cur_time INT

  SELECT @delta = DATEDIFF (mi, @last_updated, @compare_with)
  -- in sync
  IF (@delta <= @threshold)
    RETURN (0) -- in sync

  IF (@enabled = 0) 
    RETURN(0) -- in sync

  IF (@outage_weekday_mask & DATEPART(dw, GETDATE ()) > 0) -- potentially in outage window
  BEGIN
    SELECT @cur_time = DATEPART (hh, GETDATE()) * 10000 +
                       DATEPART (mi, GETDATE()) * 100 + 
                       DATEPART (ss, GETDATE())
     -- outage doesn't span midnight
    IF (@outage_start_time < @outage_end_time)
    BEGIN
      IF (@cur_time >= @outage_start_time AND @cur_time < @outage_end_time)
        RETURN(1) -- in outage
    END
     -- outage does span midnight
   ELSE IF (@outage_start_time > @outage_end_time)
   BEGIN
     IF (@cur_time >= @outage_start_time OR @cur_time < @outage_end_time)
       RETURN(1) -- in outage
   END
  END
  RETURN(-1 ) -- not in outage, not in sync
END
go

/**************************************************************/
/* sp_log_shipping_get_date_from_file                         */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_shipping_get_date_from_file...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_log_shipping_get_date_from_file' AND type = N'P')  )
  drop procedure sp_log_shipping_get_date_from_file
go
CREATE PROCEDURE sp_log_shipping_get_date_from_file 
  @db_name sysname,
  @filename NVARCHAR (500),
  @file_date DATETIME OUTPUT
AS
BEGIN
  SET NOCOUNT ON

  DECLARE @tempname NVARCHAR (500)
  IF (LEN (@filename) - (LEN(@db_name) + LEN ('_tlog_')) <= 0)
    RETURN(1) -- filename string isn't long enough
  SELECT @tempname = RIGHT (@filename, LEN (@filename) - (LEN(@db_name) + LEN ('_tlog_')))
  IF (CHARINDEX ('.',@tempname,0) > 0)
    SELECT @tempname = LEFT (@tempname, CHARINDEX ('.',@tempname,0) - 1)
  IF (LEN (@tempname) <>  8 AND LEN (@tempname) <> 12)
    RETURN (1) -- error must be yyyymmddhhmm or yyyymmdd
  IF (ISNUMERIC (@tempname) = 0 OR CHARINDEX ('.',@tempname,0) <> 0 OR CONVERT (FLOAT,SUBSTRING (@tempname, 1,8)) < 1 )
    RETURN (1) -- must be numeric, can't contain any '.' etc
  SELECT @file_date = CONVERT (DATETIME,SUBSTRING (@tempname, 1,8),112)
  IF (LEN (@tempname) = 12)
  BEGIN
    SELECT @file_date = DATEADD (hh, CONVERT (INT, SUBSTRING (@tempname,9,2)),@file_date)
    SELECT @file_date = DATEADD (mi, CONVERT (INT, SUBSTRING (@tempname,11,2)),@file_date)
  END
  RETURN (0) -- success
END
go

/**************************************************************/
/* sp_get_log_shipping_monitor_info                           */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_get_log_shipping_monitor_info...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_get_log_shipping_monitor_info' AND type = N'P')  )
  drop procedure sp_get_log_shipping_monitor_info
go
CREATE PROCEDURE sp_get_log_shipping_monitor_info
  @primary_server_name     sysname = N'%',
  @primary_database_name   sysname = N'%',
  @secondary_server_name   sysname = N'%',
  @secondary_database_name sysname = N'%'
AS BEGIN
  SET NOCOUNT ON
  DECLARE @lsp TABLE (
    primary_server_name            sysname       COLLATE database_default NOT NULL,
    primary_database_name          sysname       COLLATE database_default NOT NULL,
    secondary_server_name          sysname       COLLATE database_default NOT NULL,
    secondary_database_name        sysname       COLLATE database_default NOT NULL,
    backup_threshold               INT           NOT NULL,
    backup_threshold_alert         INT           NOT NULL,
    backup_threshold_alert_enabled BIT           NOT NULL,
    last_backup_filename           NVARCHAR(500) COLLATE database_default NOT NULL,
    last_backup_last_updated       DATETIME      NOT NULL,
    backup_outage_start_time       INT           NOT NULL,
    backup_outage_end_time         INT           NOT NULL,
    backup_outage_weekday_mask     INT           NOT NULL,
    backup_in_sync                 INT           NULL, -- 0 = in sync, -1 = out of sync, 1 = in outage window
    backup_delta                   INT           NULL,
    last_copied_filename           NVARCHAR(500) COLLATE database_default NOT NULL,
    last_copied_last_updated       DATETIME      NOT NULL,
    last_loaded_filename           NVARCHAR(500) COLLATE database_default NOT NULL,
    last_loaded_last_updated       DATETIME      NOT NULL,
    copy_delta                     INT           NULL,
    copy_enabled                   BIT           NOT NULL,
    load_enabled                   BIT           NOT NULL,
    out_of_sync_threshold          INT           NOT NULL,
    load_threshold_alert           INT           NOT NULL,
    load_threshold_alert_enabled   BIT           NOT NULL,
    load_outage_start_time         INT           NOT NULL,
    load_outage_end_time           INT           NOT NULL,
    load_outage_weekday_mask       INT           NOT NULL,
    load_in_sync                   INT           NULL, -- 0 = in sync, -1 = out of sync, 1 = in outage window
    load_delta                     INT           NULL,
    maintenance_plan_id             UNIQUEIDENTIFIER NULL,
    secondary_plan_id              UNIQUEIDENTIFIER NOT NULL)

  INSERT INTO @lsp

 SELECT
    primary_server_name,
    primary_database_name,
    secondary_server_name,
    secondary_database_name,
    backup_threshold,
    p.threshold_alert,
    p.threshold_alert_enabled,
    last_backup_filename,
    p.last_updated,
    p.planned_outage_start_time,
    p.planned_outage_end_time,
    p.planned_outage_weekday_mask,
    NULL,
    NULL,
    last_copied_filename,
    last_copied_last_updated,
    last_loaded_filename,
    last_loaded_last_updated,
    NULL,
    copy_enabled,
    load_enabled,
    out_of_sync_threshold,
    s.threshold_alert,
    s.threshold_alert_enabled,
    s.planned_outage_start_time,
    s.planned_outage_weekday_mask,
    s.planned_outage_end_time,
    NULL,
    NULL,
    maintenance_plan_id,
    secondary_plan_id
  FROM msdb.dbo.log_shipping_primaries p, msdb.dbo.log_shipping_secondaries s
  WHERE 
    p.primary_id = s.primary_id AND
    primary_server_name LIKE @primary_server_name AND
    primary_database_name LIKE @primary_database_name AND
    secondary_server_name LIKE @secondary_server_name AND
    secondary_database_name LIKE @secondary_database_name

  DECLARE @load_in_sync                   INT
  DECLARE @backup_in_sync                 INT
  DECLARE @_primary_server_name           sysname 
  DECLARE @_primary_database_name         sysname 
  DECLARE @_secondary_server_name         sysname
  DECLARE @_secondary_database_name       sysname
  DECLARE @last_loaded_last_updated       DATETIME
  DECLARE @last_loaded_filename           NVARCHAR (500)
  DECLARE @last_copied_filename           NVARCHAR (500)
  DECLARE @last_backup_last_updated       DATETIME
  DECLARE @last_backup_filename           NVARCHAR (500)
  DECLARE @backup_outage_start_time       INT
  DECLARE @backup_outage_end_time         INT
  DECLARE @backup_outage_weekday_mask     INT
  DECLARE @backup_threshold               INT
  DECLARE @backup_threshold_alert_enabled BIT
  DECLARE @load_outage_start_time         INT
  DECLARE @load_outage_end_time           INT
  DECLARE @load_outage_weekday_mask       INT
  DECLARE @load_threshold                 INT
  DECLARE @load_threshold_alert_enabled   BIT
  DECLARE @backupdt                       DATETIME
  DECLARE @restoredt                      DATETIME
  DECLARE @copydt                         DATETIME
  DECLARE @rv                             INT
  DECLARE @dt                             DATETIME
  DECLARE @copy_delta                     INT
  DECLARE @load_delta                     INT
  DECLARE @backup_delta                   INT
  DECLARE @last_copied_last_updated       DATETIME

  SELECT @dt = GETDATE ()

  DECLARE sync_update CURSOR FOR
    SELECT 
      primary_server_name, 
      primary_database_name, 
      secondary_server_name, 
      secondary_database_name,
      last_backup_filename,
      last_backup_last_updated,
      last_loaded_filename,
      last_loaded_last_updated,
      backup_outage_start_time,
      backup_outage_end_time,
      backup_outage_weekday_mask,
      backup_threshold,
      backup_threshold_alert_enabled,
      load_outage_start_time,
      load_outage_end_time,
      out_of_sync_threshold,
      load_outage_weekday_mask,
      load_threshold_alert_enabled,
      last_copied_filename,
      last_copied_last_updated
    FROM @lsp
    FOR READ ONLY

  OPEN sync_update

loop:
  FETCH NEXT FROM sync_update INTO
    @_primary_server_name, 
    @_primary_database_name, 
    @_secondary_server_name, 
    @_secondary_database_name,
    @last_backup_filename,
    @last_backup_last_updated,
    @last_loaded_filename,
    @last_loaded_last_updated,
    @backup_outage_start_time,
    @backup_outage_end_time,
    @backup_outage_weekday_mask,
    @backup_threshold,
    @backup_threshold_alert_enabled,
    @load_outage_start_time,
    @load_outage_end_time,
    @load_threshold,
    @load_outage_weekday_mask,
    @load_threshold_alert_enabled,
    @last_copied_filename,
    @last_copied_last_updated

  IF @@fetch_status <> 0
    GOTO _loop

  EXECUTE @rv = sp_log_shipping_get_date_from_file @_primary_database_name, @last_backup_filename, @backupdt OUTPUT
  IF (@rv <> 0)
    SELECT @backupdt = @last_backup_last_updated
  EXECUTE @rv = sp_log_shipping_get_date_from_file @_primary_database_name, @last_loaded_filename, @restoredt OUTPUT
  IF  (@rv <> 0)
    SELECT @restoredt = @last_loaded_last_updated
  EXECUTE @rv = sp_log_shipping_get_date_from_file @_primary_database_name, @last_copied_filename, @copydt OUTPUT
  IF  (@rv <> 0)
    SELECT @copydt = @last_copied_last_updated
  
  EXECUTE @load_in_sync = msdb.dbo.sp_log_shipping_in_sync
    @restoredt,
    @backupdt,
    @load_threshold,
    @load_outage_start_time,
    @load_outage_end_time,
    @load_outage_weekday_mask,
    @load_threshold_alert_enabled,
    @load_delta OUTPUT

  EXECUTE @backup_in_sync = msdb.dbo.sp_log_shipping_in_sync
    @last_backup_last_updated,
    @dt,
    @backup_threshold,
    @backup_outage_start_time,
    @backup_outage_end_time,
    @backup_outage_weekday_mask,
    @backup_threshold_alert_enabled,
    @backup_delta OUTPUT

  EXECUTE msdb.dbo.sp_log_shipping_in_sync
    @copydt,
    @backupdt,
    1,0,0,0,0,
    @copy_delta OUTPUT

  UPDATE @lsp 
  SET backup_in_sync = @backup_in_sync, load_in_sync  = @load_in_sync, 
    copy_delta = @copy_delta, load_delta = @load_delta, backup_delta = @backup_delta
  WHERE primary_server_name = @_primary_server_name AND
    secondary_server_name = @_secondary_server_name AND
    primary_database_name = @_primary_database_name AND
    secondary_database_name = @_secondary_database_name 
  GOTO loop
_loop:
  CLOSE sync_update
  DEALLOCATE sync_update
  SELECT * FROM @lsp
END
go

/**************************************************************/
/* sp_update_log_shipping_monitor_info                        */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_update_log_shipping_monitor_info...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_update_log_shipping_monitor_info' AND type = N'P')  )
  DROP PROCEDURE sp_update_log_shipping_monitor_info
go
CREATE PROCEDURE sp_update_log_shipping_monitor_info
  @primary_server_name                 sysname,
  @primary_database_name               sysname,
  @secondary_server_name               sysname,
  @secondary_database_name             sysname,
  @backup_threshold                    INT = NULL,
  @backup_threshold_alert              INT = NULL,
  @backup_threshold_alert_enabled      BIT = NULL,
  @backup_outage_start_time            INT = NULL,
  @backup_outage_end_time              INT = NULL,
  @backup_outage_weekday_mask          INT = NULL,
  @copy_enabled                        BIT = NULL,
  @load_enabled                        BIT = NULL,
  @out_of_sync_threshold               INT = NULL,
  @out_of_sync_threshold_alert         INT = NULL,
  @out_of_sync_threshold_alert_enabled BIT = NULL,
  @out_of_sync_outage_start_time       INT = NULL,
  @out_of_sync_outage_end_time         INT = NULL,
  @out_of_sync_outage_weekday_mask     INT = NULL
AS BEGIN
  SET NOCOUNT ON
  DECLARE @_backup_threshold                    INT
  DECLARE @_backup_threshold_alert              INT
  DECLARE @_backup_threshold_alert_enabled      BIT
  DECLARE @_backup_outage_start_time            INT
  DECLARE @_backup_outage_end_time              INT
  DECLARE @_backup_outage_weekday_mask          INT
  DECLARE @_copy_enabled                        BIT
  DECLARE @_load_enabled                        BIT
  DECLARE @_out_of_sync_threshold               INT
  DECLARE @_out_of_sync_threshold_alert         INT
  DECLARE @_out_of_sync_threshold_alert_enabled BIT
  DECLARE @_out_of_sync_outage_start_time       INT
  DECLARE @_out_of_sync_outage_end_time         INT
  DECLARE @_out_of_sync_outage_weekday_mask     INT

  -- check that the primary exists
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name))
  BEGIN
    DECLARE @pp sysname
    SELECT @pp = @primary_server_name + N'.' + @primary_database_name
    RAISERROR (14262, 16, 1, N'primary_server_name.primary_database_name', @pp)
    RETURN (1) -- error
  END

  -- check that the secondary exists
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_secondaries WHERE secondary_server_name = @secondary_server_name AND secondary_database_name = @secondary_database_name))
  BEGIN
    DECLARE @sp sysname
    SELECT @sp = @secondary_server_name + N'.' + @secondary_database_name
    RAISERROR (14262, 16, 1, N'secondary_server_name.secondary_database_name', @sp)
    RETURN (1) -- error
  END

  -- load the original variables

 SELECT
    @_backup_threshold                    = backup_threshold,
    @_backup_threshold_alert              = p.threshold_alert,
    @_backup_threshold_alert_enabled      = p.threshold_alert_enabled,
    @_backup_outage_start_time            = p.planned_outage_start_time,
    @_backup_outage_end_time              = p.planned_outage_end_time,
    @_backup_outage_weekday_mask          = p.planned_outage_weekday_mask,
    @_copy_enabled                        = copy_enabled,
    @_load_enabled                        = load_enabled,
    @_out_of_sync_threshold               = out_of_sync_threshold,
    @_out_of_sync_threshold_alert         = s.threshold_alert,
    @_out_of_sync_threshold_alert_enabled = s.threshold_alert_enabled,
    @_out_of_sync_outage_start_time       = s.planned_outage_start_time,
    @_out_of_sync_outage_weekday_mask     = s.planned_outage_weekday_mask,
    @_out_of_sync_outage_end_time         = s.planned_outage_end_time
  FROM msdb.dbo.log_shipping_primaries p, msdb.dbo.log_shipping_secondaries s
  WHERE 
    p.primary_id            = s.primary_id           AND
    primary_server_name     = @primary_server_name   AND
    primary_database_name   = @primary_database_name AND
    secondary_server_name   = @secondary_server_name AND
    secondary_database_name = @secondary_database_name

  SELECT @_backup_threshold                    = ISNULL (@backup_threshold,                    @_backup_threshold)
  SELECT @_backup_threshold_alert              = ISNULL (@backup_threshold_alert,              @_backup_threshold_alert)
  SELECT @_backup_threshold_alert_enabled      = ISNULL (@backup_threshold_alert_enabled,      @_backup_threshold_alert_enabled)
  SELECT @_backup_outage_start_time            = ISNULL (@backup_outage_start_time,            @_backup_outage_start_time)
  SELECT @_backup_outage_end_time              = ISNULL (@backup_outage_end_time,              @_backup_outage_end_time)
  SELECT @_backup_outage_weekday_mask          = ISNULL (@backup_outage_weekday_mask,          @_backup_outage_weekday_mask)
  SELECT @_copy_enabled                        = ISNULL (@copy_enabled,                        @_copy_enabled)
  SELECT @_load_enabled                        = ISNULL (@load_enabled,                        @_load_enabled)
  SELECT @_out_of_sync_threshold               = ISNULL (@out_of_sync_threshold,               @_out_of_sync_threshold)
  SELECT @_out_of_sync_threshold_alert         = ISNULL (@out_of_sync_threshold_alert,         @_out_of_sync_threshold_alert)
  SELECT @_out_of_sync_threshold_alert_enabled = ISNULL (@out_of_sync_threshold_alert_enabled, @_out_of_sync_threshold_alert_enabled)
  SELECT @_out_of_sync_outage_start_time       = ISNULL (@out_of_sync_outage_start_time,       @_out_of_sync_outage_start_time)
  SELECT @_out_of_sync_outage_end_time         = ISNULL (@out_of_sync_outage_end_time,         @_out_of_sync_outage_end_time)
  SELECT @_out_of_sync_outage_weekday_mask     = ISNULL (@out_of_sync_outage_weekday_mask,     @_out_of_sync_outage_weekday_mask)

  -- updates
  UPDATE msdb.dbo.log_shipping_primaries SET
    backup_threshold            = @_backup_threshold,
    threshold_alert             = @_backup_threshold_alert,
    threshold_alert_enabled     = @_backup_threshold_alert_enabled,
    planned_outage_start_time   = @_backup_outage_start_time,
    planned_outage_end_time     = @_backup_outage_end_time,
    planned_outage_weekday_mask = @_backup_outage_weekday_mask
  WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name

  UPDATE msdb.dbo.log_shipping_secondaries SET
    copy_enabled                = @_copy_enabled,
    load_enabled                = @_load_enabled,
    out_of_sync_threshold       = @_out_of_sync_threshold,
    threshold_alert             = @_out_of_sync_threshold_alert,
    threshold_alert_enabled     = @_out_of_sync_threshold_alert_enabled,
    planned_outage_start_time   = @_out_of_sync_outage_start_time,
    planned_outage_end_time     = @_out_of_sync_outage_weekday_mask,
    planned_outage_weekday_mask = @_out_of_sync_outage_end_time
  WHERE secondary_server_name = @secondary_server_name AND secondary_database_name = @secondary_database_name
RETURN(0)
END
go

/**************************************************************/
/* sp_delete_log_shipping_monitor_info                        */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_delete_log_shipping_monitor_info...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_delete_log_shipping_monitor_info' AND type = N'P')  )
  DROP PROCEDURE sp_delete_log_shipping_monitor_info
go
CREATE PROCEDURE sp_delete_log_shipping_monitor_info
  @primary_server_name                 sysname,
  @primary_database_name               sysname,
  @secondary_server_name               sysname,
  @secondary_database_name             sysname
AS BEGIN
  -- check that the primary exists
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name))
  BEGIN
    DECLARE @pp sysname
    SELECT @pp = @primary_server_name + N'.' + @primary_database_name
    RAISERROR (14262, 16, 1, N'primary_server_name.primary_database_name', @pp)
    RETURN (1) -- error
  END

  -- check that the secondary exists
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_secondaries WHERE secondary_server_name = @secondary_server_name AND secondary_database_name = @secondary_database_name))
  BEGIN
    DECLARE @sp sysname
    SELECT @sp = @secondary_server_name + N'.' + @secondary_database_name
    RAISERROR (14262, 16, 1, N'secondary_server_name.secondary_database_name', @sp)
    RETURN (1) -- error
  END

  BEGIN TRANSACTION

  -- delete the secondary
  DELETE FROM msdb.dbo.log_shipping_secondaries WHERE secondary_server_name = @secondary_server_name AND secondary_database_name = @secondary_database_name
  IF (@@error <> 0)
    goto rollback_quit

  -- if there are no more secondaries for this primary then delete it
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries p, msdb.dbo.log_shipping_secondaries s WHERE p.primary_id = s.primary_id AND primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name))
  BEGIN
    DELETE FROM msdb.dbo.log_shipping_primaries WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name
    IF (@@error <> 0)
      goto rollback_quit
  END
 COMMIT TRANSACTION
 RETURN (0)

rollback_quit:
  ROLLBACK TRANSACTION
  RETURN(1) -- Failure
END
go 

/**************************************************************/
/* sp_remove_log_shipping_monitor_account                     */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_remove_log_shipping_monitor_account...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_remove_log_shipping_monitor_account' AND type = N'P')  )
  DROP PROCEDURE sp_remove_log_shipping_monitor_account
go

CREATE PROCEDURE sp_remove_log_shipping_monitor_account
AS
BEGIN
  SET NOCOUNT ON
  EXECUTE sp_dropuser N'log_shipping_monitor_probe'
  EXECUTE sp_droplogin N'log_shipping_monitor_probe'
END
go

/**************************************************************/
/* sp_log_shipping_monitor_backup                             */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_shipping_monitor_backup...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_log_shipping_monitor_backup' AND type = N'P')  )
  drop procedure sp_log_shipping_monitor_backup
go

CREATE PROCEDURE sp_log_shipping_monitor_backup AS
BEGIN
  DECLARE @primary_id                  sysname
  DECLARE @primary_server_name         sysname 
  DECLARE @primary_database_name       sysname 
  DECLARE @maintenance_plan_id         UNIQUEIDENTIFIER
  DECLARE @backup_threshold            INT
  DECLARE @threshold_alert             INT 
  DECLARE @threshold_alert_enabled     BIT 
  DECLARE @last_backup_filename        sysname 
  DECLARE @last_updated                DATETIME
  DECLARE @planned_outage_start_time   INT
  DECLARE @planned_outage_end_time     INT 
  DECLARE @planned_outage_weekday_mask INT
  DECLARE @sync_status                 INT
  DECLARE @backup_delta                INT
  DECLARE @delta_string                NVARCHAR (10)
  DECLARE @dt                             DATETIME

  SELECT @dt = GETDATE ()

  SET NOCOUNT ON

  DECLARE bmlsp_cur CURSOR FOR
    SELECT primary_id, 
           primary_server_name, 
           primary_database_name, 
         maintenance_plan_id, 
           backup_threshold, 
           threshold_alert, 
           threshold_alert_enabled, 
           last_backup_filename, 
           last_updated,
           planned_outage_start_time, 
           planned_outage_end_time, 
           planned_outage_weekday_mask 
    FROM msdb.dbo.log_shipping_primaries
    FOR READ ONLY

  OPEN bmlsp_cur
loop:
  FETCH NEXT FROM bmlsp_cur 
  INTO @primary_id, 
       @primary_server_name, 
      @primary_database_name, 
      @maintenance_plan_id,
       @backup_threshold, 
      @threshold_alert, 
      @threshold_alert_enabled, 
      @last_backup_filename, 
      @last_updated, 
      @planned_outage_start_time,
       @planned_outage_end_time, 
      @planned_outage_weekday_mask

  IF @@FETCH_STATUS <> 0 -- nothing more to fetch, finish the loop
    GOTO _loop

  EXECUTE @sync_status = sp_log_shipping_in_sync
    @last_updated,
    @dt,
     @backup_threshold,
   @planned_outage_start_time,
   @planned_outage_end_time,
    @planned_outage_weekday_mask,
   @threshold_alert_enabled,
   @backup_delta OUTPUT

   IF (@sync_status < 0)
   BEGIN
     SELECT @delta_string = CONVERT (NVARCHAR(10), @backup_delta)
     RAISERROR (@threshold_alert, 16, 1, @primary_server_name, @primary_database_name, @delta_string)
   END

  GOTO loop
_loop:
  CLOSE bmlsp_cur
  DEALLOCATE bmlsp_cur
END
go

/**************************************************************/
/* sp_log_shipping_monitor_restore                            */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_shipping_monitor_restore...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_log_shipping_monitor_restore' AND type = N'P')  )
  drop procedure sp_log_shipping_monitor_restore
go
CREATE PROCEDURE sp_log_shipping_monitor_restore AS
BEGIN
  SET NOCOUNT ON
  DECLARE @primary_id                  INT
  DECLARE @secondary_server_name       sysname
  DECLARE @secondary_database_name     sysname
  DECLARE @secondary_plan_id           UNIQUEIDENTIFIER
  DECLARE @out_of_sync_threshold       INT 
  DECLARE @threshold_alert             INT 
  DECLARE @threshold_alert_enabled     BIT 
  DECLARE @last_loaded_filename        NVARCHAR (500)
  DECLARE @last_backup_filename        NVARCHAR (500) 
  DECLARE @primary_database_name       sysname
  DECLARE @last_loaded_last_updated    DATETIME
  DECLARE @last_backup_last_updated    DATETIME
  DECLARE @planned_outage_start_time   INT 
  DECLARE @planned_outage_end_time     INT 
  DECLARE @planned_outage_weekday_mask INT
  DECLARE @sync_status                 INT
  DECLARE @sync_delta                  INT
  DECLARE @delta_string                NVARCHAR(10)

  SET NOCOUNT ON
  DECLARE @backupdt  DATETIME
  DECLARE @restoredt DATETIME
  DECLARE @rv        INT
  DECLARE rmlsp_cur CURSOR FOR
    SELECT s.primary_id, 
      s.secondary_server_name, 
      s.secondary_database_name, 
      s.secondary_plan_id, 
      s.out_of_sync_threshold, 
      s.threshold_alert, 
      s.threshold_alert_enabled, 
      s.last_loaded_filename, 
      s.last_loaded_last_updated,
      p.last_backup_filename,
      p.last_updated,
      p.primary_database_name,
      s.planned_outage_start_time, 
      s.planned_outage_end_time, 
      s.planned_outage_weekday_mask 
    FROM msdb.dbo.log_shipping_secondaries s 
    INNER JOIN msdb.dbo.log_shipping_primaries p 
    ON s.primary_id = p.primary_id
    FOR READ ONLY

  OPEN rmlsp_cur
loop:
  FETCH NEXT FROM rmlsp_cur 
  INTO @primary_id, 
      @secondary_server_name, 
         @secondary_database_name, 
         @secondary_plan_id, 
       @out_of_sync_threshold, 
         @threshold_alert, 
         @threshold_alert_enabled, 
         @last_loaded_filename, 
         @last_loaded_last_updated,
       @last_backup_filename,
       @last_backup_last_updated,
       @primary_database_name,
       @planned_outage_start_time, 
         @planned_outage_end_time, 
         @planned_outage_weekday_mask 

  IF @@FETCH_STATUS <> 0 -- nothing more to fetch, finish the loop
    GOTO _loop

  EXECUTE @rv = sp_log_shipping_get_date_from_file @primary_database_name, @last_backup_filename, @backupdt OUTPUT
  IF (@rv <> 0)
    SELECT @backupdt = @last_backup_last_updated
  
  EXECUTE @rv = sp_log_shipping_get_date_from_file @primary_database_name, @last_loaded_filename, @restoredt OUTPUT
  IF  (@rv <> 0)
    SELECT @restoredt = @last_loaded_last_updated

  EXECUTE @sync_status = sp_log_shipping_in_sync
    @restoredt,
    @backupdt,
     @out_of_sync_threshold,
     @planned_outage_start_time,
     @planned_outage_end_time,
    @planned_outage_weekday_mask,
    @threshold_alert_enabled,
    @sync_delta OUTPUT

   IF (@sync_status < 0)
   BEGIN
     SELECT @delta_string = CONVERT (NVARCHAR(10), @sync_delta)
     RAISERROR (@threshold_alert, 16, 1, @secondary_server_name, @secondary_database_name, @delta_string)
   END

  GOTO loop
_loop:
  CLOSE rmlsp_cur
  DEALLOCATE rmlsp_cur
END
go

/**************************************************************/
/* sp_change_monitor_role                                     */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_change_monitor_role...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_change_monitor_role' AND type = N'P')  )
  DROP PROCEDURE sp_change_monitor_role
go
CREATE PROCEDURE sp_change_monitor_role
  @primary_server     sysname,
  @secondary_server   sysname,
  @database           sysname,
  @new_source         NVARCHAR (128)
AS BEGIN
  SET NOCOUNT ON

  BEGIN TRANSACTION

  -- drop the secondary
  DELETE FROM msdb.dbo.log_shipping_secondaries 
    WHERE secondary_server_name = @secondary_server AND secondary_database_name = @database

  IF (@@ROWCOUNT <> 1)
  BEGIN
      ROLLBACK TRANSACTION
      RAISERROR (14442,-1,-1)
      return(1)
  END

  -- let everyone know that we are the new primary
  UPDATE msdb.dbo.log_shipping_primaries 
    SET primary_server_name = @secondary_server, primary_database_name = @database, source_directory = @new_source
    WHERE primary_server_name = @primary_server AND primary_database_name = @database

  IF (@@ROWCOUNT <> 1)
  BEGIN
      ROLLBACK TRANSACTION
      RAISERROR (14442,-1,-1)
      return(1)
  END
  COMMIT TRANSACTION

END
go

/**************************************************************/
/* sp_create_log_shipping_monitor_account                     */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_create_log_shipping_monitor_account...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_create_log_shipping_monitor_account' AND type = N'P')  )
  drop procedure sp_create_log_shipping_monitor_account
go
CREATE PROCEDURE sp_create_log_shipping_monitor_account @password sysname
AS
BEGIN
  DECLARE @rv INT
  SET NOCOUNT ON
-- raise an error if the password already exists
  if exists(select * from master.dbo.syslogins where loginname = N'log_shipping_monitor_probe')
  begin
    raiserror(15025,-1,-1,N'log_shipping_monitor_probe')
    RETURN (1) -- error
  end

  IF (@password = N'')
  BEGIN
    EXECUTE @rv = sp_addlogin N'log_shipping_monitor_probe', @defdb = N'msdb'
    IF @@error <>0 or @rv <> 0
      RETURN (1) -- error
  END
  ELSE
  BEGIN
    EXECUTE @rv = sp_addlogin N'log_shipping_monitor_probe', @password, N'msdb'
    IF @@error <>0 or @rv <> 0
      RETURN (1) -- error
  END

  EXECUTE @rv = sp_grantdbaccess N'log_shipping_monitor_probe', N'log_shipping_monitor_probe'
  IF @@error <>0 or @rv <> 0
    RETURN (1) -- error

  GRANT UPDATE ON log_shipping_primaries   TO log_shipping_monitor_probe
  GRANT UPDATE ON log_shipping_secondaries TO log_shipping_monitor_probe
  GRANT SELECT ON log_shipping_primaries   TO log_shipping_monitor_probe
  GRANT SELECT ON log_shipping_secondaries TO log_shipping_monitor_probe

  RETURN (0)
END
go

/**************************************************************/
/* INTEGRATION SERVICES SECTION                               */
/**************************************************************/

USE msdb
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sysssispackages]'))
BEGIN
CREATE TABLE [dbo].[sysssispackages] (
   [name] [sysname] NOT NULL ,
   [id] [uniqueidentifier] NOT NULL ,
   [description] [nvarchar] (1024) NULL ,
   [createdate] [datetime] NOT NULL ,
   [folderid] [uniqueidentifier] NOT NULL ,
   [ownersid] [varbinary] (85) NOT NULL ,
   [packagedata] [image] NOT NULL ,
   [packageformat] [int] NOT NULL,
   [packagetype] [int] NOT NULL CONSTRAINT [DF__sysssispackages] DEFAULT (0),
   [vermajor] [int] NOT NULL,
   [verminor] [int] NOT NULL,
   [verbuild] [int] NOT NULL,
   [vercomments] [nvarchar] (1024) NULL,
   [verid] [uniqueidentifier] NOT NULL,
   [isencrypted] [bit] NOT NULL CONSTRAINT [DF__sysssispackages_2] DEFAULT (0),
   [readrolesid] [varbinary] (85) NULL,
   [writerolesid] [varbinary] (85) NULL,
   CONSTRAINT [pk_sysssispackages] PRIMARY KEY NONCLUSTERED 
   (
      [folderid],
      [name]
   ) ON [PRIMARY] ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
else
BEGIN
IF NOT EXISTS (
  select * from msdb.dbo.syscolumns where name='isencrypted' and id =
      (select id from msdb.dbo.sysobjects where name='sysssispackages'))
BEGIN
  ALTER TABLE [dbo].[sysssispackages] ADD [isencrypted] [bit] NOT NULL CONSTRAINT [DF__sysssispackages_2] DEFAULT (0)
  ALTER TABLE [dbo].[sysssispackages] ADD [readrolesid] [varbinary] (85) NULL
  ALTER TABLE [dbo].[sysssispackages] ADD [writerolesid] [varbinary] (85) NULL
END
ELSE
BEGIN
  IF NOT EXISTS (
    select * from msdb.dbo.syscolumns where name='readrolesid' and id =
        (select id from msdb.dbo.sysobjects where name='sysssispackages'))
  BEGIN
    ALTER TABLE [dbo].[sysssispackages] DROP COLUMN [readrole]
    ALTER TABLE [dbo].[sysssispackages] DROP COLUMN [writerole]
    ALTER TABLE [dbo].[sysssispackages] ADD [readrolesid] [varbinary] (85) NULL
    ALTER TABLE [dbo].[sysssispackages] ADD [writerolesid] [varbinary] (85) NULL
  END
END
END
GO

/**************************************************************/
/* sysmaintplan_plans                                      */
/**************************************************************/
PRINT ''
PRINT 'Creating view sysmaintplan_plans...'
go
IF (NOT OBJECT_ID(N'dbo.sysmaintplan_plans', 'V') IS NULL)
  DROP VIEW sysmaintplan_plans
go
CREATE VIEW sysmaintplan_plans
AS
   SELECT
   s.name AS [name],
   s.id AS [id],
   s.description AS [description],
   s.createdate AS [create_date],
   suser_sname(s.ownersid) AS [owner],
   s.vermajor AS [version_major],
   s.verminor AS [version_minor],
   s.verbuild AS [version_build],
   s.vercomments AS [version_comments],
   ISNULL((select TOP 1 msx_plan from sysmaintplan_subplans where plan_id = s.id), 0) AS [from_msx],
   CASE WHEN (NOT EXISTS (select TOP 1 msx_job_id 
                          from sysmaintplan_subplans subplans, sysjobservers jobservers
                          where plan_id = s.id 
                          and msx_job_id is not null
                          and subplans.msx_job_id = jobservers.job_id
                          and server_id != 0)) 
        then 0 
        else 1 END AS [has_targets]
   FROM
   msdb.dbo.sysssispackages AS s
   WHERE
   (s.folderid = '08aa12d5-8f98-4dab-a4fc-980b150a5dc8' and s.packagetype = 6)
go

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sysssispackagefolders]'))
BEGIN
CREATE TABLE [dbo].[sysssispackagefolders] (
   [folderid] [uniqueidentifier] NOT NULL ,
   [parentfolderid] [uniqueidentifier] NULL ,
   [foldername] [sysname] NOT NULL ,
   CONSTRAINT [PK_sysssispackagefolders] PRIMARY KEY NONCLUSTERED 
   (
      [folderid]
   )  ON [PRIMARY],
   CONSTRAINT [U_sysssispackagefoldersuniquepath] UNIQUE NONCLUSTERED 
   (
      [parentfolderid], 
      [foldername]
   ) ON [PRIMARY]
) ON [PRIMARY]
END
GO

-- WARNING! IMPORTANT! If you change sysssislog table schema,
-- be sure to update \dts\src\dtr\runtime\logproviders.cpp !!!

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sysssislog]'))
BEGIN
CREATE TABLE [dbo].[sysssislog] (
   [id] [int] NOT NULL IDENTITY PRIMARY KEY,
   [event] [sysname] NOT NULL,
   [computer] [nvarchar] (128) NOT NULL,
   [operator] [nvarchar] (128) NOT NULL,
   [source] [nvarchar] (1024) NOT NULL,
   [sourceid] [uniqueidentifier] NOT NULL,
   [executionid] [uniqueidentifier] NOT NULL,
   [starttime] [datetime] NOT NULL,
   [endtime] [datetime] NOT NULL,
   [datacode] [int] NOT NULL,
   [databytes] [image] NULL,
   [message] [nvarchar] (2048) NOT NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_addlogentry]'))
    drop procedure [dbo].[sp_ssis_addlogentry]   

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_addlogentry]
  @event sysname,
  @computer nvarchar(128),
  @operator nvarchar(128),
  @source nvarchar(1024),
  @sourceid uniqueidentifier,
  @executionid uniqueidentifier,
  @starttime datetime,
  @endtime datetime,
  @datacode int,
  @databytes image,
  @message nvarchar(2048)
AS
  INSERT INTO sysssislog (
      event,
      computer,
      operator,
      source,
      sourceid,
      executionid,
      starttime,
      endtime,
      datacode,
      databytes,
      message )
  VALUES (
      @event,
      @computer,
      @operator,
      @source,
      @sourceid,
      @executionid,
      @starttime,
      @endtime,
      @datacode,
      @databytes,
      @message )
  RETURN 0
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_listpackages]'))
    drop procedure [dbo].[sp_ssis_listpackages]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_listpackages]
  @folderid uniqueidentifier
AS
  SELECT
      name,
      id,
      description,
      createdate,
      folderid,
      datalength(packagedata),
      vermajor,
      verminor,
      verbuild,
      vercomments,
      verid
  FROM
      sysssispackages
  WHERE
      [folderid] = @folderid
  ORDER BY
      name
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_listfolders]'))
    drop procedure [dbo].[sp_ssis_listfolders]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_listfolders]
  @parentfolderid uniqueidentifier = NULL
AS
  SELECT
   folderid,
   parentfolderid,
   foldername
  FROM
      sysssispackagefolders
  WHERE
      [parentfolderid] = @parentfolderid OR 
      (@parentfolderid IS NULL AND [parentfolderid] IS NULL)
  ORDER BY 
      foldername
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_deletepackage]'))
    drop procedure [dbo].[sp_ssis_deletepackage]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_deletepackage]
  @name sysname,
  @folderid uniqueidentifier
AS
  DECLARE @sid varbinary(85)
  DECLARE @writerolesid varbinary(85)
  DECLARE @writerole nvarchar(128)
  SELECT
      @sid = [ownersid],
      @writerolesid = [writerolesid]
  FROM
      sysssispackages
  WHERE
      [name] = @name AND
      [folderid] = @folderid
  IF @sid IS NOT NULL
  BEGIN
      --// The row exists, check security
      IF @writerolesid IS NOT NULL
      BEGIN
          SELECT @writerole = [name] FROM sys.database_principals WHERE [type] = ''R'' AND [sid] = @writerolesid
          IF @writerole IS NULL SET @writerole = ''db_ssisadmin''
      END
      IF @writerole IS NULL
      BEGIN
          IF (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
          BEGIN
              IF (@sid<>SUSER_SID()) OR (IS_MEMBER(''db_ssisltduser'')<>1)
              BEGIN
                  RAISERROR (14307, -1, -1, @name)
                  RETURN 1  -- Failure
              END
          END
      END
      ELSE
      BEGIN
          -- If writerrole is set for this package, 
          -- Allow sysadmins and the members of writer role to delete this package
          IF (IS_MEMBER(@writerole)<>1)  AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
          BEGIN
              IF (@sid<>SUSER_SID()) OR (IS_MEMBER(''db_ssisltduser'')<>1)
              BEGIN
                  RAISERROR (14307, -1, -1, @name)
                  RETURN 1  -- Failure
              END
          END
      END
  END
  DELETE FROM sysssispackages
  WHERE
      [name] = @name AND
      [folderid] = @folderid
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_deletefolder]'))
    drop procedure [dbo].[sp_ssis_deletefolder]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_deletefolder]
  @folderid uniqueidentifier
AS
   DECLARE @name  sysname
   DECLARE @count int

   IF @folderid = ''00000000-0000-0000-0000-000000000000''
   BEGIN
      RAISERROR (14307, -1, -1, ''00000000-0000-0000-0000-000000000000'')
      RETURN 1  -- Failure
   END

   SELECT
       @name = [foldername]
   FROM
       sysssispackagefolders
   WHERE
       [folderid] = @folderid
   IF @name IS NOT NULL
   BEGIN
       --// The row exists, check security
       IF (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
       BEGIN
           IF (IS_MEMBER(''db_ssisltduser'')<>1)
           BEGIN
               RAISERROR (14307, -1, -1, @name)
               RETURN 1  -- Failure
           END
       END
   END

   -- Get the number of packages in this folder
   SELECT
      @count = count(*)
   FROM
      sysssispackages
   WHERE
      [folderid] = @folderid

   -- Are there any packages in this folder
   IF @count > 0
   BEGIN
      -- Yes, do not delete
      RAISERROR (14593, -1, -1, @name)
      RETURN 1  -- Failure
   END

   -- Get the number of folders in this folder
   SELECT
      @count = count(*)
   FROM
      sysssispackagefolders
   WHERE
      [parentfolderid] = @folderid

   -- Are there any folders in this folder
   IF @count > 0
   BEGIN
      -- Yes, do not delete
      RAISERROR (14593, -1, -1, @name)
      RETURN 1  -- Failure
   END

   DELETE FROM sysssispackagefolders
   WHERE
       [folderid] = @folderid
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_getpackage]'))
    drop procedure [dbo].[sp_ssis_getpackage]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_getpackage]
  @name sysname,
  @folderid uniqueidentifier
AS
  DECLARE @sid varbinary(85)
  DECLARE @isencrypted bit
  DECLARE @readrolesid varbinary(85)
  DECLARE @readrole nvarchar(128)
  --// Check security, if the row exists
  SELECT @sid = [ownersid], @readrolesid = [readrolesid] FROM sysssispackages WHERE [name] = @name AND [folderid] = @folderid
  IF @sid IS NOT NULL
  BEGIN
      IF @readrolesid IS NOT NULL
      BEGIN
          SELECT @readrole = [name] FROM sys.database_principals WHERE [type] = ''R'' AND [sid] = @readrolesid
          IF @readrole IS NULL SET @readrole = ''db_ssisadmin''
      END
      IF @readrole IS NOT NULL
      BEGIN
          IF (IS_MEMBER(@readrole)<>1) AND (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
          BEGIN
              IF (IS_MEMBER(''db_ssisltduser'')<>1) OR (@sid<>SUSER_SID())
              BEGIN
                  RAISERROR (14307, -1, -1, @name)
                  RETURN 1  -- Failure
              END
          END
      END
      ELSE
      BEGIN
          IF (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1) AND (IS_MEMBER(''db_ssisoperator'')<>1)
          BEGIN
              IF (IS_MEMBER(''db_ssisltduser'')<>1) OR (@sid<>SUSER_SID())
              BEGIN
                  RAISERROR (14586, -1, -1, @name)
                  RETURN 1  -- Failure
              END
          END
      END
  END

  SELECT
      packagedata
  FROM
      sysssispackages
  WHERE
      [name] = @name AND
      [folderid] = @folderid
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_getfolder]'))
    drop procedure [dbo].[sp_ssis_getfolder]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_getfolder]
  @name sysname,
  @parentfolderid uniqueidentifier
AS
  SELECT
   folder.folderid,
   folder.foldername,
   folder.parentfolderid,
   parent.foldername
  FROM
      sysssispackagefolders folder 
  LEFT OUTER JOIN 
      sysssispackagefolders parent
  ON
      folder.parentfolderid = parent.folderid
  WHERE
      folder.foldername = @name AND
      (folder.parentfolderid = @parentfolderid OR 
      (@parentfolderid IS NULL AND folder.parentfolderid IS NULL))
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_putpackage]'))
    drop procedure [dbo].[sp_ssis_putpackage]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_putpackage]
  @name sysname,
  @id uniqueidentifier,
  @description nvarchar(1024),
  @createdate datetime,
  @folderid uniqueidentifier,
  @packagedata image,
  @packageformat int,
  @packagetype int,
  @vermajor int,
  @verminor int,
  @verbuild int,
  @vercomments nvarchar(1024),
  @verid uniqueidentifier
AS
  SET NOCOUNT ON
  DECLARE @sid varbinary(85)
  DECLARE @writerolesid varbinary(85)
  DECLARE @writerole nvarchar(128)
  --// Determine if we should INSERT or UPDATE
  SELECT @sid = [ownersid], @writerolesid = [writerolesid] FROM sysssispackages WHERE [name] = @name AND [folderid] = @folderid
  IF @sid IS NOT NULL
  BEGIN
      --// The row exists, check security
      IF @writerolesid IS NOT NULL
      BEGIN
          SELECT @writerole = [name] FROM sys.database_principals WHERE [type] = ''R'' AND [sid] = @writerolesid
          IF @writerole IS NULL SET @writerole = ''db_ssisadmin''
      END
      IF @writerole IS NULL
      BEGIN
          IF (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
          BEGIN
              IF (@sid<>SUSER_SID()) OR (IS_MEMBER(''db_ssisltduser'')<>1)
              BEGIN
                  RAISERROR (14307, -1, -1, @name)
                  RETURN 1  -- Failure
              END
          END
      END
      ELSE
      BEGIN
          IF (IS_MEMBER(@writerole)<>1) AND (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
          BEGIN
              IF (@sid<>SUSER_SID()) OR (IS_MEMBER(''db_ssisltduser'')<>1)
              BEGIN
                  RAISERROR (14307, -1, -1, @name)
                  RETURN 1  -- Failure
              END
          END
      END
      --// Security check passed, UPDATE now
      UPDATE sysssispackages
      SET
          id = @id,
          description = @description,
          createdate = @createdate,
          packagedata = @packagedata,
          packageformat = @packageformat,
          packagetype = @packagetype,
          vermajor = @vermajor,
          verminor = @verminor,
          verbuild = @verbuild,
          vercomments = @vercomments,
          verid = @verid
      WHERE
          name = @name AND folderid = @folderid
  END
  ELSE
  BEGIN
      --// The row does not exist, check security
      IF (IS_MEMBER(''db_ssisltduser'')<>1) AND (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
      BEGIN
          RAISERROR (14307, -1, -1, @name)
          RETURN 1  -- Failure
      END
      --// Security check passed, INSERT now
      INSERT INTO sysssispackages (
          name,
          id,
          description,
          createdate,
          folderid,
          ownersid,
          packagedata,
          packageformat,
          packagetype,
          vermajor,
          verminor,
          verbuild,
          vercomments,
          verid )
      VALUES (
          @name,
          @id,
          @description,
          @createdate,
          @folderid,
          SUSER_SID(),
          @packagedata,
          @packageformat,
          @packagetype,
          @vermajor,
          @verminor,
          @verbuild,
          @vercomments,
          @verid )
  END
  RETURN 0    -- SUCCESS
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_checkexists]'))
    drop procedure [dbo].[sp_ssis_checkexists]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_checkexists]
  @name sysname,
  @folderid uniqueidentifier
AS
  SET NOCOUNT ON
  SELECT TOP 1 1 FROM sysssispackages WHERE [name] = @name AND [folderid] = @folderid
  RETURN 0    -- SUCCESS
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_addfolder]'))
    drop procedure [dbo].[sp_ssis_addfolder]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_addfolder]
  @parentfolderid uniqueidentifier,
  @name sysname,
  @folderid uniqueidentifier = NULL
AS
   --Check security
   IF (IS_MEMBER(''db_ssisltduser'')<>1) AND (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
   BEGIN
       RAISERROR (14591, -1, -1, @name)
       RETURN 1  -- Failure
   END

   --// Security check passed, INSERT now
   INSERT INTO sysssispackagefolders (folderid, parentfolderid, foldername)
   VALUES (ISNULL(@folderid, NEWID()), @parentfolderid, @name)
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_renamefolder]'))
    drop procedure [dbo].[sp_ssis_renamefolder]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_renamefolder]
  @folderid uniqueidentifier,
  @name sysname
AS
   --Check security
   IF (IS_MEMBER(''db_ssisltduser'')<>1) AND (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
   BEGIN
       RAISERROR (14591, -1, -1, @name)
       RETURN 1  -- Failure
   END

   --// Security check passed, INSERT now
   UPDATE sysssispackagefolders
   SET [foldername] = @name
   WHERE [folderid] = @folderid
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_setpackageroles]'))
DROP PROCEDURE [dbo].[sp_ssis_setpackageroles]
GO

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_setpackageroles]
  @name sysname,
  @folderid uniqueidentifier,
  @readrole nvarchar (128),
  @writerole nvarchar (128)
AS
  SET NOCOUNT ON
  DECLARE @sid varbinary(85)
  --// Determine if we should INSERT or UPDATE
  SELECT @sid = ownersid FROM sysssispackages WHERE name = @name AND folderid = @folderid
  IF @sid IS NOT NULL
  BEGIN
      --// The row exists, check security
      IF (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
      BEGIN
          IF (@sid<>SUSER_SID())
          BEGIN
              RAISERROR (14307, -1, -1, @name)
              RETURN 1  -- Failure
          END
      END
      --// Security check passed, UPDATE now
      DECLARE @readrolesid varbinary(85)
      DECLARE @writerolesid varbinary(85)
      SELECT @readrolesid = [sid] FROM sys.database_principals WHERE [type] = ''R'' AND [name] = @readrole
      SELECT @writerolesid = [sid] FROM sys.database_principals WHERE [type] = ''R'' AND [name] = @writerole
      IF @readrolesid IS NULL AND @readrole IS NOT NULL
      BEGIN
          RAISERROR (15014, -1, -1, @readrole)
          RETURN 1
      END
      IF @writerolesid IS NULL AND @writerole IS NOT NULL
      BEGIN
          RAISERROR (15014, -1, -1, @writerole)
          RETURN 1
      END
      UPDATE sysssispackages
      SET
          [readrolesid] = @readrolesid,
          [writerolesid] = @writerolesid
      WHERE
          name = @name AND folderid = @folderid
  END
  ELSE
  BEGIN
      RAISERROR (14307, -1, -1, @name)
      RETURN 1  -- Failure
  END
  RETURN 0    -- SUCCESS
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_getpackageroles]'))
DROP PROCEDURE [dbo].[sp_ssis_getpackageroles]
GO

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_getpackageroles]
  @name sysname,
  @folderid uniqueidentifier
AS
  DECLARE @readrolesid varbinary(85)
  DECLARE @writerolesid varbinary(85)
  DECLARE @readrole nvarchar(128)
  DECLARE @writerole nvarchar(128)
  SELECT
      @readrolesid = [readrolesid],
      @writerolesid = [writerolesid]
  FROM
      sysssispackages
  WHERE
      [name] = @name AND
      [folderid] = @folderid
  SELECT @readrole = [name] FROM sys.database_principals WHERE [type] = ''R'' AND [sid] = @readrolesid
  SELECT @writerole = [name] FROM sys.database_principals WHERE [type] = ''R'' AND [sid] = @writerolesid
  SELECT @readrole AS readrole, @writerole AS writerole
'
GO


GRANT  EXECUTE  ON [dbo].[sp_ssis_addlogentry]     TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_addlogentry]     TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_deletepackage]   TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_deletepackage]   TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_deletepackage]   TO [db_ssisoperator]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getpackage]      TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getpackage]      TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getpackage]      TO [db_ssisoperator]
GRANT  EXECUTE  ON [dbo].[sp_ssis_listpackages]    TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_listpackages]    TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_listpackages]    TO [db_ssisoperator]
GRANT  EXECUTE  ON [dbo].[sp_ssis_putpackage]      TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_putpackage]      TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_putpackage]      TO [db_ssisoperator]
GRANT  EXECUTE  ON [dbo].[sp_ssis_checkexists]     TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_checkexists]     TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_checkexists]     TO [db_ssisoperator]
GRANT  EXECUTE  ON [dbo].[sp_ssis_listfolders]     TO [db_ssisadmin]  
GRANT  EXECUTE  ON [dbo].[sp_ssis_listfolders]     TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_listfolders]     TO [db_ssisoperator] 
GRANT  EXECUTE  ON [dbo].[sp_ssis_deletefolder]    TO [db_ssisadmin]  
GRANT  EXECUTE  ON [dbo].[sp_ssis_deletefolder]    TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_addfolder]       TO [db_ssisadmin]  
GRANT  EXECUTE  ON [dbo].[sp_ssis_addfolder]       TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_renamefolder]    TO [db_ssisadmin]  
GRANT  EXECUTE  ON [dbo].[sp_ssis_renamefolder]    TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getfolder]       TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getfolder]       TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getfolder]       TO [db_ssisoperator]
GRANT  EXECUTE  ON [dbo].[sp_ssis_setpackageroles] TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_setpackageroles] TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getpackageroles] TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getpackageroles] TO [db_ssisltduser]
GO

GRANT  ALL      ON [dbo].[sysssislog]            TO [db_ssisadmin]
GRANT  INSERT   ON [dbo].[sysssislog]            TO [db_ssisltduser]
GRANT  SELECT   ON [dbo].[sysssislog]            TO [db_ssisltduser]
GRANT  INSERT   ON [dbo].[sysssislog]            TO [db_ssisoperator]
GRANT  SELECT   ON [dbo].[sysssislog]            TO [db_ssisoperator]
GO

-- Maintenance Plans
 -- Allow SQLAgent on target servers to gather information about
 -- maintenance plans from the master.
GRANT EXECUTE ON sp_maintplan_subplans_by_job  TO SQLAgentUserRole
GRANT EXECUTE ON sp_maintplan_subplans_by_job  TO TargetServersRole


/**************************************************************/
/*                                                            */
/*  D  A  T  A     C  O  L  L  E  C  T  O  R                  */
/*                                                            */
/**************************************************************/
USE msdb
GO

---------------------------------------------------------------
-- Data Collector: Security: Database Principals
---------------------------------------------------------------

PRINT ''
PRINT 'Create dc_operator role...'
IF ( NOT EXISTS (SELECT * FROM sys.database_principals 
                    WHERE name = N'dc_operator' AND type = 'R'))
BEGIN
    CREATE ROLE [dc_operator]
END
ELSE -- if the role exists check to see if it has members
BEGIN
    IF NOT EXISTS (SELECT rm.member_principal_id
                FROM sys.database_principals dp 
                INNER JOIN sys.database_role_members rm ON rm.role_principal_id = dp.principal_id
                WHERE name = N'dc_operator' AND type = 'R')
    BEGIN
        -- if the role has no members drop and recreate it
        DROP ROLE [dc_operator]
        CREATE ROLE [dc_operator]
    END
END
GO

EXECUTE sp_addrolemember @rolename = 'db_ssisltduser' , 
                   @membername = 'dc_operator' 
GO

EXECUTE sp_addrolemember @rolename = 'db_ssisoperator' , 
                   @membername = 'dc_operator' 
GO

EXECUTE sp_addrolemember @rolename = 'SQLAgentUserRole' , 
                   @membername = 'dc_operator' 
GO

PRINT ''
PRINT 'Create dc_admin role...'
IF ( NOT EXISTS (SELECT * FROM sys.database_principals 
                    WHERE name = N'dc_admin' AND type = 'R'))
BEGIN
    CREATE ROLE [dc_admin]
END
ELSE -- if the role exists check to see if it has members
BEGIN
    IF NOT EXISTS (SELECT rm.member_principal_id
                FROM sys.database_principals dp 
                INNER JOIN sys.database_role_members rm ON rm.role_principal_id = dp.principal_id
                WHERE name = N'dc_admin' AND type = 'R')
    BEGIN
        -- if the role has no members drop and recreate it
        DROP ROLE [dc_admin]
        CREATE ROLE [dc_admin]
    END
END
GO

EXECUTE sp_addrolemember @rolename = 'dc_operator' , 
                   @membername = 'dc_admin' 
GO

PRINT ''
PRINT 'Create dc_proxy role...'
IF ( NOT EXISTS (SELECT * FROM sys.database_principals 
                    WHERE name = N'dc_proxy' AND type = 'R'))
BEGIN
    CREATE ROLE [dc_proxy]
END
ELSE -- if the role exists check to see if it has members
BEGIN
    IF NOT EXISTS (SELECT rm.member_principal_id
                FROM sys.database_principals dp 
                INNER JOIN sys.database_role_members rm ON rm.role_principal_id = dp.principal_id
                WHERE name = N'dc_proxy' AND type = 'R')
    BEGIN
        -- if the role has no members drop and recreate it
        DROP ROLE [dc_proxy]
        CREATE ROLE [dc_proxy]
    END
END
GO

EXECUTE sp_addrolemember @rolename = 'db_ssisltduser' , 
                   @membername = 'dc_proxy' 
GO

EXECUTE sp_addrolemember @rolename = 'db_ssisoperator' , 
                   @membername = 'dc_proxy' 
GO

PRINT ''
PRINT 'Create loginless user that has ownership of data collector agent-related securables...'
IF (NOT EXISTS(SELECT * FROM sys.database_principals WHERE NAME = 'MS_DataCollectorInternalUser'))
BEGIN
    CREATE USER [MS_DataCollectorInternalUser] WITHOUT LOGIN
END
GO

EXECUTE sp_addrolemember @rolename = 'db_ssisoperator' , 
                   @membername = 'MS_DataCollectorInternalUser' 
GO

EXECUTE sp_addrolemember @rolename = 'SQLAgentUserRole' , 
                   @membername = 'MS_DataCollectorInternalUser' 
GO

EXECUTE sp_addrolemember @rolename = 'dc_admin' , 
                   @membername = 'MS_DataCollectorInternalUser' 
GO

GRANT IMPERSONATE ON USER::[MS_DataCollectorInternalUser] TO [dc_admin];
GO

---------------------------------------------------------------
-- Configuration store
---------------------------------------------------------------
IF (OBJECT_ID(N'[dbo].[syscollector_config_store_internal]', 'U') IS NULL)
BEGIN
    PRINT 'Creating table [dbo].[syscollector_config_store_internal]...'
    CREATE TABLE [dbo].[syscollector_config_store_internal] (
        parameter_name                nvarchar(128) NOT NULL,
        parameter_value                sql_variant NULL,
        CONSTRAINT [PK_syscollector_config_store_internal_paremeter_name] PRIMARY KEY CLUSTERED (parameter_name ASC)
        )
END
GO

IF (NOT OBJECT_ID(N'[dbo].[syscollector_config_store]', 'V') IS NULL)
BEGIN
    PRINT 'Dropping view [dbo].[syscollector_config_store]...'
    DROP VIEW [dbo].[syscollector_config_store]
END
GO

PRINT 'Creating view [dbo].[syscollector_config_store]...'
GO
CREATE VIEW [dbo].[syscollector_config_store]
AS
    SELECT
        s.parameter_name,
        s.parameter_value
    FROM 
        [dbo].[syscollector_config_store_internal] s
GO

-- populate config store with known name and value pairs
IF (NOT EXISTS(SELECT * FROM [dbo].[syscollector_config_store_internal]
                WHERE parameter_name = N'MDWInstance'))
BEGIN
INSERT INTO [dbo].[syscollector_config_store_internal] (
    parameter_name, 
    parameter_value
)
VALUES
(
    N'MDWInstance',
    NULL
)
END

IF (NOT EXISTS(SELECT * FROM [dbo].[syscollector_config_store_internal]
                WHERE parameter_name = N'MDWDatabase'))
BEGIN
INSERT INTO [dbo].[syscollector_config_store_internal] (
    parameter_name, 
    parameter_value
)
VALUES
(
    N'MDWDatabase',
    NULL
)
END

IF (NOT EXISTS(SELECT * FROM [dbo].[syscollector_config_store_internal]
                WHERE parameter_name = 'CollectorEnabled'))
BEGIN
INSERT INTO [dbo].[syscollector_config_store_internal] (
    parameter_name, 
    parameter_value
)
VALUES
(
    'CollectorEnabled',
    0
)
END

IF (NOT EXISTS(SELECT * FROM [dbo].[syscollector_config_store_internal]
                WHERE parameter_name = 'CacheWindow'))
BEGIN
INSERT INTO [dbo].[syscollector_config_store_internal] (
    parameter_name, 
    parameter_value
)
VALUES
(
    'CacheWindow',
    1  --By default, the collector will keep 1 window's worth of uploads
)
END

IF (NOT EXISTS(SELECT * FROM [dbo].[syscollector_config_store_internal]
                WHERE parameter_name = 'CacheDirectory'))
BEGIN
INSERT INTO [dbo].[syscollector_config_store_internal] (
    parameter_name, 
    parameter_value
)
VALUES
(
    'CacheDirectory',
    NULL
)
END

GO

---------------------------------------------------------------
-- Access collector level properties
---------------------------------------------------------------

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_verify_collector_state]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_verify_collector_state]...'
    DROP PROCEDURE [dbo].[sp_syscollector_verify_collector_state]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_verify_collector_state]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_verify_collector_state]
    @desired_state          int
WITH EXECUTE AS OWNER -- 'MS_DataCollectorInternalUser'
AS
BEGIN
    DECLARE @collector_enabled      INT
    SET @collector_enabled = CONVERT(int, (SELECT parameter_value FROM dbo.syscollector_config_store_internal
                            WHERE parameter_name = 'CollectorEnabled'))

    IF (@collector_enabled IS NULL)
    BEGIN
        RAISERROR(14691, -1, -1)
        RETURN(1)
    END

    IF (@collector_enabled = 0) AND (@desired_state = 1)
    BEGIN
        RAISERROR(14681, -1, -1)
        RETURN(1)
    END

    IF (@collector_enabled = 1) AND (@desired_state = 0)
    BEGIN
        RAISERROR(14690, -1, -1)
        RETURN(1)
    END

    RETURN(0)
END
GO

IF (NOT OBJECT_ID('dbo.sp_syscollector_set_warehouse_instance_name', 'P') IS NULL)
BEGIN
    DROP PROCEDURE [dbo].[sp_syscollector_set_warehouse_instance_name]
END
GO

PRINT ''
PRINT 'Creating procedure [dbo].[sp_syscollector_set_warehouse_instance_name]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_set_warehouse_instance_name]
    @instance_name                    sysname = NULL
AS
BEGIN
    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_admin')
        RETURN(1) -- Failure
    END

    -- Check if the collector is disabled
    DECLARE @retVal int
    EXEC @retVal = [dbo].[sp_syscollector_verify_collector_state] @desired_state = 0
    IF (@retVal <> 0)
        RETURN (1)

    UPDATE [msdb].[dbo].[syscollector_config_store_internal]
    SET parameter_value = @instance_name
    WHERE parameter_name = N'MDWInstance'

    RETURN (0)
END
GO

IF (NOT OBJECT_ID('dbo.sp_syscollector_set_warehouse_database_name', 'P') IS NULL)
BEGIN
    DROP PROCEDURE [dbo].[sp_syscollector_set_warehouse_database_name]
END
GO

PRINT ''
PRINT 'Creating procedure [dbo].[sp_syscollector_set_warehouse_database_name]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_set_warehouse_database_name]
    @database_name                    sysname = NULL
AS
BEGIN
    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_admin')
        RETURN(1) -- Failure
    END

    -- Check if the collector is disabled
    DECLARE @retVal int
    EXEC @retVal = [dbo].[sp_syscollector_verify_collector_state] @desired_state = 0
    IF (@retVal <> 0)
        RETURN (1)

    UPDATE [msdb].[dbo].[syscollector_config_store_internal]
    SET parameter_value = @database_name
    WHERE parameter_name = N'MDWDatabase'

    RETURN (0)
END
GO

IF (NOT OBJECT_ID('dbo.sp_syscollector_set_cache_directory', 'P') IS NULL)
BEGIN
    DROP PROCEDURE [dbo].[sp_syscollector_set_cache_directory]
END
GO

PRINT ''
PRINT 'Creating procedure [dbo].[sp_syscollector_set_cache_directory]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_set_cache_directory]
    @cache_directory                    nvarchar(255) = NULL
AS
BEGIN
    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_admin')
        RETURN(1) -- Failure
    END

    SET @cache_directory = NULLIF(LTRIM(RTRIM(@cache_directory)), N'')

    -- Check if the collector is disabled
    DECLARE @retVal int
    EXEC @retVal = [dbo].[sp_syscollector_verify_collector_state] @desired_state = 0
    IF (@retVal <> 0)
        RETURN (1)

    UPDATE [msdb].[dbo].[syscollector_config_store_internal]
    SET parameter_value = @cache_directory
    WHERE parameter_name = N'CacheDirectory'

    RETURN (0)
END
GO

IF (NOT OBJECT_ID('dbo.sp_syscollector_set_cache_window', 'P') IS NULL)
BEGIN
    DROP PROCEDURE [dbo].[sp_syscollector_set_cache_window]
END
GO

PRINT ''
PRINT 'Creating procedure [dbo].[sp_syscollector_set_cache_window]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_set_cache_window]
    @cache_window                    int = 1
AS
BEGIN
    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_admin')
        RETURN(1) -- Failure
    END

    -- Check if the collector is disabled
    DECLARE @retVal int
    EXEC @retVal = [dbo].[sp_syscollector_verify_collector_state] @desired_state = 0
    IF (@retVal <> 0)
        RETURN (1)

    IF (@cache_window < -1)
    BEGIN
        RAISERROR(14687, -1, -1, @cache_window)
        RETURN(1)
    END

    UPDATE [msdb].[dbo].[syscollector_config_store_internal]
    SET parameter_value = @cache_window
    WHERE parameter_name = N'CacheWindow'

    RETURN (0)
END
GO

IF (NOT OBJECT_ID('dbo.sp_syscollector_get_warehouse_connection_string', 'P') IS NULL)
BEGIN
    DROP PROCEDURE [dbo].[sp_syscollector_get_warehouse_connection_string]
END
GO

PRINT ''
PRINT 'Creating procedure [dbo].[sp_syscollector_get_warehouse_connection_string]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_get_warehouse_connection_string]
    @connection_string              nvarchar(512) = NULL OUTPUT
AS
BEGIN
    DECLARE @instance_name sysname
    DECLARE @database_name sysname
    DECLARE @user_name sysname
    DECLARE @password sysname

    SELECT @instance_name = CONVERT(sysname,parameter_value)
    FROM [msdb].[dbo].[syscollector_config_store_internal]
    WHERE parameter_name = N'MDWInstance'

    IF (@instance_name IS NULL)
    BEGIN
        RAISERROR(14686, -1, -1)
        RETURN (1)
    END
    
    -- '"' is the delimiter for the sql client connection string
    SET @instance_name = QUOTENAME(@instance_name, '"')

    SELECT @database_name = CONVERT(sysname,parameter_value)
    FROM [msdb].[dbo].[syscollector_config_store_internal]
    WHERE parameter_name = N'MDWDatabase'

    IF (@database_name IS NULL)
    BEGIN
        RAISERROR(14686, -1, -1)
        RETURN (1)
    END

    SET @database_name = QUOTENAME(@database_name, '"')

    SET @connection_string = N'Data Source=' + @instance_name + N';Application Name="Data Collector - MDW";Initial Catalog=' + @database_name
    SET @connection_string = @connection_string + N';Use Encryption for Data=true;Trust Server Certificate=true;Provider=SQLNCLI10;Integrated Security=SSPI;Connect Timeout=60;';

    RETURN (0)
END
GO

-- 
-- Return the highest version of the Management Data Warehouse that this
-- Collector is no compatible with. Anything higher than this version
-- is fine.
--
-- If you change this number, make sure to change the corresponding versions
-- in HighestIncompatibleMDWVersion.cs
--
IF (NOT OBJECT_ID('dbo.fn_syscollector_highest_incompatible_mdw_version', 'FN') IS NULL)
BEGIN
    DROP FUNCTION [dbo].[fn_syscollector_highest_incompatible_mdw_version]
END
GO

PRINT ''
PRINT 'Creating function [dbo].[fn_syscollector_highest_incompatible_mdw_version]...'
GO
CREATE FUNCTION [dbo].[fn_syscollector_highest_incompatible_mdw_version]()
RETURNS nvarchar(50)
BEGIN
    RETURN '10.00.1300.13'  -- CTP6
END
GO
---------------------------------------------------------------
-- Collection set
---------------------------------------------------------------

IF (OBJECT_ID(N'[dbo].[syscollector_collection_sets_internal]', 'U') IS NULL)
BEGIN
    PRINT 'Creating table [dbo].[syscollector_collection_sets_internal]...'
    CREATE TABLE [dbo].[syscollector_collection_sets_internal] (
        collection_set_id            int IDENTITY NOT NULL,
        collection_set_uid            uniqueidentifier NOT NULL,      
        schedule_uid                uniqueidentifier NULL,          -- schedule to run collection or upload
        name                        sysname NOT NULL,               -- name of the collection set, must be unique
        name_id                        int NULL,                        -- sysmessage id of the name of the set (for localizing system collection set)
        target                        nvarchar(max) NULL,             -- future use
        is_running                    bit default 0 NOT NULL,         -- is the collection set active
        proxy_id                    int NULL,                       -- proxy to use to run the collection set
        is_system                    bit NOT NULL,                   -- indicates MS-shipped collection set
        collection_job_id            uniqueidentifier NULL,          -- id of the collection job
        upload_job_id                uniqueidentifier NULL,          -- id of the upload job
        collection_mode                smallint default 0 NOT NULL,    -- 0 - cached, 1 - non-cached
        logging_level                smallint default 2 NOT NULL,    -- 0 - errors only, 1 - errors & warnings, 2 - detailed
        description                    nvarchar(4000) NULL,            -- description of the set
        description_id                int NULL,                        -- sysmessage id of the description of the set (for localizing system collection set)
        days_until_expiration       smallint NOT NULL,              -- how long to keep the data from this collection set
        dump_on_any_error           bit default 0 NOT NULL,         -- configure SQL dumper to dump on any SSIS errors
        dump_on_codes               nvarchar(max) NULL,             -- configure SQL dumper to dump when we hit one of the specified SSIS errors 
        CONSTRAINT [PK_syscollector_collection_sets_internal] PRIMARY KEY CLUSTERED (collection_set_id ASC),
        CONSTRAINT [UQ_syscollector_collection_sets_internal_name] UNIQUE (name)
        )
    ALTER TABLE syscollector_collection_sets_internal
        ADD CONSTRAINT [FK_syscollector_collection_sets_internal_sysproxies] FOREIGN KEY(proxy_id)
        REFERENCES sysproxies (proxy_id)
    ALTER TABLE syscollector_collection_sets_internal
        ADD CONSTRAINT [FK_syscollector_collection_sets_collection_sysjobs] FOREIGN KEY(collection_job_id)
        REFERENCES sysjobs (job_id)
    ALTER TABLE syscollector_collection_sets_internal
        ADD CONSTRAINT [FK_syscollector_collection_sets_upload_sysjobs] FOREIGN KEY(upload_job_id)
        REFERENCES sysjobs(job_id)
END ELSE BEGIN
    IF (NOT EXISTS (SELECT * FROM msdb.dbo.syscolumns WHERE name = N'name_id' AND id = OBJECT_ID(N'[dbo].[syscollector_collection_sets_internal]')))
    BEGIN
        PRINT 'Altering table [dbo].[syscollector_collection_sets_internal]...'
        ALTER TABLE [dbo].[syscollector_collection_sets_internal] ADD name_id int NULL
    END
    
    IF (NOT EXISTS (SELECT * FROM msdb.dbo.syscolumns WHERE name = N'description_id' AND id = OBJECT_ID(N'[dbo].[syscollector_collection_sets_internal]')))
    BEGIN
        PRINT 'Altering table [dbo].[syscollector_collection_sets_internal]...'
        ALTER TABLE [dbo].[syscollector_collection_sets_internal] ADD description_id int NULL
    END
        IF (NOT EXISTS (SELECT * FROM msdb.dbo.syscolumns WHERE name = N'dump_on_any_error' AND id = OBJECT_ID(N'[dbo].[syscollector_collection_sets_internal]')))
    BEGIN
        PRINT 'Altering table [dbo].[syscollector_collection_sets_internal]...'
        ALTER TABLE [dbo].[syscollector_collection_sets_internal] ADD dump_on_any_error bit default 0
    END
    
    IF (NOT EXISTS (SELECT * FROM msdb.dbo.syscolumns WHERE name = N'dump_on_codes' AND id = OBJECT_ID(N'[dbo].[syscollector_collection_sets_internal]')))
    BEGIN
        PRINT 'Altering table [dbo].[syscollector_collection_sets_internal]...'
        ALTER TABLE [dbo].[syscollector_collection_sets_internal] ADD dump_on_codes nvarchar(max) NULL
    END    
END
GO

IF (NOT OBJECT_ID(N'[dbo].[syscollector_collection_sets]', 'V') IS NULL)
BEGIN
    PRINT 'Dropping view [dbo].[syscollector_collection_sets]...'
    DROP VIEW [dbo].[syscollector_collection_sets]
END
GO

PRINT 'Creating view [dbo].[syscollector_collection_sets]...'
GO
CREATE VIEW [dbo].[syscollector_collection_sets]
AS
    SELECT 
        s.collection_set_id,
        s.collection_set_uid,
        CASE 
            WHEN s.name_id IS NULL THEN s.name 
            ELSE FORMATMESSAGE(s.name_id)
        END AS name,        
        s.target,
        s.is_system,
        s.is_running,
        s.collection_mode,
        s.proxy_id,
        s.schedule_uid,
        s.collection_job_id,
        s.upload_job_id,
        s.logging_level,
        s.days_until_expiration,
        CASE 
            WHEN s.description_id IS NULL THEN s.description
            ELSE FORMATMESSAGE(s.description_id)
        END AS description,
        s.dump_on_any_error,
        s.dump_on_codes
    FROM 
        [dbo].[syscollector_collection_sets_internal] AS s
GO

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_verify_collection_set]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_verify_collection_set]...'
    DROP PROCEDURE [dbo].[sp_syscollector_verify_collection_set]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_verify_collection_set]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_verify_collection_set]
    @collection_set_id        int = NULL OUTPUT,
    @name                    sysname = NULL OUTPUT
AS
BEGIN
    IF (@name IS NOT NULL)
    BEGIN
        -- Remove any leading/trailing spaces from parameters
        SET @name =            NULLIF(LTRIM(RTRIM(@name)), N'')
    END

    IF (@collection_set_id IS NULL AND @name IS NULL)
    BEGIN
        RAISERROR(14624, -1, -1, '@collection_set_id, @name')
        RETURN(1)
    END

    IF (@collection_set_id IS NOT NULL AND @name IS NOT NULL)
    BEGIN
        IF (NOT EXISTS(SELECT *
                        FROM dbo.syscollector_collection_sets
                        WHERE collection_set_id = @collection_set_id
                        AND name = @name))
        BEGIN
            DECLARE @errMsg NVARCHAR(196)
            SELECT @errMsg = CONVERT(NVARCHAR(36), @collection_set_id) + ', ' + @name
            RAISERROR(14262, -1, -1, '@collection_set_id, @name', @errMsg)
            RETURN(1)
        END
    END
    -- Check id
    ELSE IF (@collection_set_id IS NOT NULL)
    BEGIN
        SELECT @name = name
        FROM dbo.syscollector_collection_sets
        WHERE (collection_set_id = @collection_set_id)
    
        -- the view would take care of all the permissions issues.
        IF (@name IS NULL) 
        BEGIN
            DECLARE @collection_set_id_as_char VARCHAR(36)
            SELECT @collection_set_id_as_char = CONVERT(VARCHAR(36), @collection_set_id)
            RAISERROR(14262, -1, -1, '@collection_set_id', @collection_set_id_as_char)
            RETURN(1) -- Failure
        END
    END
    -- Check name
    ELSE IF (@name IS NOT NULL)
    BEGIN
        -- get the corresponding collection_set_id (if the collection set exists)
        SELECT @collection_set_id = collection_set_id
        FROM dbo.syscollector_collection_sets
        WHERE (name = @name)

        -- the view would take care of all the permissions issues.
        IF (@collection_set_id IS NULL)
        BEGIN
            RAISERROR(14262, -1, -1, '@name', @name)
            RETURN(1) -- Failure
        END
    END
    RETURN (0)
END
GO

-- Create one schedule that starts when SQL Agent starts so that all continuous
-- collection jobs can attach to it, the schedule has to be accessible to the internal dc user that owns agent objects
-- EXECUTE AS USER = 'MS_DataCollectorInternalUser';

IF (NOT EXISTS (SELECT * FROM sysschedules_localserver_view WHERE name = N'RunAsSQLAgentServiceStartSchedule'))
BEGIN
    EXEC dbo.sp_add_schedule
        @schedule_name = N'RunAsSQLAgentServiceStartSchedule',
        @freq_type = 0x40,            -- FREQTYPE_AUTOSTART
        @freq_interval = 1
END

-- REVERT;
GO


IF (NOT OBJECT_ID('[dbo].[sp_syscollector_create_jobs]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_create_jobs]...'
    DROP PROCEDURE [dbo].[sp_syscollector_create_jobs]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_create_jobs]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_create_jobs]
    @collection_set_id        int,
    @collection_set_uid        uniqueidentifier,
    @collection_set_name    sysname,
    @proxy_id                int = NULL,
    @schedule_id            int = NULL,
    @collection_mode        smallint,
    @collection_job_id        uniqueidentifier OUTPUT,
    @upload_job_id            uniqueidentifier OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_syscollector_create_jobs
    ELSE
        BEGIN TRANSACTION

    BEGIN TRY

    -- job step names and commands shared between collection modes
    DECLARE @collection_set_id_as_char nvarchar(36)

    DECLARE @collection_step_command nvarchar(512)
    DECLARE @upload_step_command nvarchar(512)
    DECLARE @autostop_step_command nvarchar(512)
    DECLARE @purge_step_command nvarchar(1024)

    DECLARE @collection_step_name sysname
    DECLARE @upload_step_name sysname
    DECLARE @autostop_step_name sysname
    DECLARE @purge_step_name sysname

    DECLARE @instance_name  nvarchar(128)
    SET @instance_name = ISNULL(UPPER(CONVERT(nvarchar(128), SERVERPROPERTY('InstanceName'))), N'MSSQLSERVER')

    DECLARE @job_name sysname
    DECLARE @job_id uniqueidentifier        
    DECLARE @description nvarchar(512)

    IF(@collection_set_id IS NOT NULL)
    BEGIN
        SET @collection_set_id_as_char = CONVERT(NVARCHAR(36), @collection_set_id)
        SET @collection_step_command = 
            N'dcexec -c -s ' + @collection_set_id_as_char + N' -i ' + QUOTENAME(@instance_name, N'"') + 
            N' -m ' + CONVERT(NVARCHAR(36), @collection_mode);
        SET @upload_step_command = 
            N'dcexec -u -s ' + @collection_set_id_as_char + N' -i ' + QUOTENAME(@instance_name, N'"');
        SET @autostop_step_command =
            N'exec dbo.sp_syscollector_stop_collection_set @collection_set_id=' + @collection_set_id_as_char 
            + N', @stop_collection_job = 0';  -- do not stop the collection job, otherwise you will abort yourself!
        SET @purge_step_command = 
            N'
            EXEC [dbo].[sp_syscollector_purge_collection_logs]
            '
    END

    -- verify that the proxy_id exists
    IF (@proxy_id IS NOT NULL)
    BEGIN
        DECLARE @proxy_name sysname
        DECLARE @retVal int
        -- this will throw an error of proxy_id does not exist
        EXEC @retVal = msdb.dbo.sp_verify_proxy_identifiers '@proxy_name', '@proxy_id', @proxy_name OUTPUT, @proxy_id OUTPUT
        IF (@retVal <> 0)
            RETURN (0)
    END

    -- add jobs, job steps and attach schedule separately for different modes
    IF (@collection_mode = 1)    -- non-cached mode
    BEGIN
        -- create 1 job and 2 steps, first for collection & upload, second for log purging
        SET @job_name = N'collection_set_' + @collection_set_id_as_char + '_noncached_collect_and_upload'
        SET @collection_step_name = @job_name + '_collect'
        SET @upload_step_name = @job_name + '_upload'
        SET @purge_step_name = @job_name + '_purge_logs'
        SET @description = N'Data Collector job for collection set ' + QUOTENAME(@collection_set_name)

        -- add agent job and job server
        EXEC dbo.sp_add_job 
            @job_name        = @job_name,
            @category_id    = 8, -- N'Data Collector'
            @enabled        = 0,
            @description    = @description,
            @job_id            = @job_id OUTPUT
        
        EXEC dbo.sp_add_jobserver
            @job_id            = @job_id,
            @server_name    = N'(local)'

        -- add both collect and upload job steps to the same job
        EXEC dbo.sp_add_jobstep
            @job_id                = @job_id,
            @step_name            = @collection_step_name,
            @subsystem            = 'CMDEXEC',
            @command            = @collection_step_command,
            @on_success_action    =  3,        -- go to the next job step (purge the log)
            @on_fail_action        =  2,        -- quit with failure
            @proxy_id            = @proxy_id,
            @flags              = 16        -- Write log to table (append to existing history)

        EXEC dbo.sp_add_jobstep
            @job_id                = @job_id,
            @step_name            = @purge_step_name,
            @subsystem            = 'TSQL',
            @database_name        = 'msdb',
            @command            = @purge_step_command,
            @on_success_action    =  3,        -- go to the next job step (upload)
            @on_fail_action        =  3,        -- go to the next job step (upload)
            @proxy_id            = NULL,
            @flags                = 16        -- write log to table (append to existing history)

        EXEC dbo.sp_add_jobstep
            @job_id                = @job_id,
            @step_name            = @upload_step_name,
            @subsystem            = 'CMDEXEC',
            @command            = @upload_step_command,
            @on_success_action    =  1,        -- quit with success
            @on_fail_action        =  2,        -- quit with failure
            @proxy_id            = @proxy_id,
            @flags              = 16        -- Write log to table (append to existing history)

        IF @schedule_id IS NOT NULL
        BEGIN
            -- attach the schedule
            EXEC dbo.sp_attach_schedule
                @job_id            = @job_id,
                @schedule_id    = @schedule_id
        END

        SET @upload_job_id = @job_id
        SET @collection_job_id = @job_id
    END

    IF (@collection_mode = 0) -- cached mode
    BEGIN
        -- create 2 jobs for collect and upload
        -- add to collect job an extra step that autostops collection called in case collect job fails
        DECLARE @upload_job_name        sysname
        DECLARE @collection_job_name    sysname
        SET @upload_job_name = N'collection_set_' + @collection_set_id_as_char + '_upload'
        SET @collection_job_name = N'collection_set_' + @collection_set_id_as_char + '_collection'

        SET @collection_step_name = @collection_job_name + '_collect'
        SET @autostop_step_name = @collection_job_name + '_autostop'
        SET @upload_step_name = @upload_job_name + '_upload'
        SET @purge_step_name = @upload_job_name + '_purge_logs'

        -- modify the collection step to pass in the stop event name passed in by agent
        SET @collection_step_command = @collection_step_command + N' -e $' + N'(ESCAPE_NONE(' + N'STOPEVENT))'

        -- add agent job and job server
        EXEC dbo.sp_add_job 
            @job_name        = @upload_job_name,
            @category_id    = 8, -- N'Data Collector'
            @enabled        = 0,
            @job_id            = @upload_job_id OUTPUT
        
        EXEC dbo.sp_add_jobserver
            @job_id            = @upload_job_id,
            @server_name    = N'(local)'

        EXEC dbo.sp_add_job 
            @job_name        = @collection_job_name,
            @category_id    = 8, -- N'Data Collector'
            @enabled        = 0,
            @job_id            = @collection_job_id OUTPUT

        EXEC dbo.sp_add_jobserver
            @job_id            = @collection_job_id,
            @server_name    = N'(local)'

        -- add upload job step to upload job and collection job
        -- step to collection job separately
        EXEC dbo.sp_add_jobstep
            @job_id                = @upload_job_id,
            @step_name            = @purge_step_name,
            @subsystem            = 'TSQL',
            @database_name        = 'msdb',
            @command            = @purge_step_command,
            @on_success_action    =  3,        -- go to next job step (upload)
            @on_fail_action        =  3,        -- go to next job step (upload)
            @proxy_id            = NULL,
            @flags                = 16        -- write log to table (append to existing history)

        EXEC dbo.sp_add_jobstep
            @job_id                = @upload_job_id,
            @step_name            = @upload_step_name,
            @subsystem            = 'CMDEXEC',
            @command            = @upload_step_command,
            @on_success_action    =  1,        -- quit with success
            @on_fail_action        =  2,        -- quit with failure
            @proxy_id            = @proxy_id

        EXEC dbo.sp_add_jobstep
            @job_id                = @collection_job_id,
            @step_name            = @collection_step_name,
            @subsystem            = 'CMDEXEC',
            @command            = @collection_step_command,
            @on_success_action    =  1,        -- quit with success
            @on_fail_action        =  3,        -- go to next job step (auto-stop)
            @proxy_id            = @proxy_id,
            @flags              = 80 -- 16 (write log to table (append to existing history) 
                                     -- + 64 (create a stop event and pass it to the command line)

        EXEC dbo.sp_add_jobstep
            @job_id                = @collection_job_id,
            @step_name            = @autostop_step_name,
            @subsystem            = 'TSQL',
            @database_name        = 'msdb',
            @command            = @autostop_step_command,
            @on_success_action    =  2,        -- quit with failure
            @on_fail_action        =  2,        -- quit with failure
            @proxy_id            = NULL,
            @flags                = 16        -- write log to table (append to existing history)

        -- attach the input schedule to the upload job
        EXEC dbo.sp_attach_schedule
            @job_id            = @upload_job_id,
            @schedule_id    = @schedule_id

        -- attach the RunAsSQLAgentServiceStartSchedule to the collection job
        EXEC dbo.sp_attach_schedule
            @job_id            = @collection_job_id,
            @schedule_name    = N'RunAsSQLAgentServiceStartSchedule'
    END

    IF (@TranCounter = 0)
        COMMIT TRANSACTION
    RETURN (0)

    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_syscollector_create_jobs

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);

        RETURN (1)
    END CATCH
END
GO

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_create_collection_set]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_create_collection_set]...'
    DROP PROCEDURE [dbo].[sp_syscollector_create_collection_set]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_create_collection_set]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_create_collection_set]
    @name                        sysname,
    @target                        nvarchar(128) = NULL,
    @collection_mode            smallint = 0,    -- 0: cached, 1: non-cached
    @days_until_expiration      smallint = 730, -- two years
    @proxy_id                   int = NULL,     -- mutual exclusive; must specify either proxy_id or proxy_name to identify the proxy
    @proxy_name                    sysname = NULL,
    @schedule_uid               uniqueidentifier = NULL, 
    @schedule_name              sysname = NULL, -- mutual exclusive; must specify either schedule_uid or schedule_name to identify the schedule
    @logging_level                smallint = 1,
    @description                nvarchar(4000) = NULL,
    @collection_set_id            int OUTPUT,
    @collection_set_uid            uniqueidentifier = NULL OUTPUT
WITH EXECUTE AS OWNER -- 'MS_DataCollectorInternalUser'
AS
BEGIN
    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_create_collection_set
    ELSE
        BEGIN TRANSACTION

    BEGIN TRY

    -- Security check (role membership)
    EXECUTE AS CALLER;
    IF (NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        REVERT;
        RAISERROR(14677, -1, -1, 'dc_admin')
        RETURN (1)
    END
    REVERT;

    -- Remove any leading/trailing spaces from parameters
    SET @name                    = NULLIF(LTRIM(RTRIM(@name)), N'')
    SET @proxy_name                = NULLIF(LTRIM(RTRIM(@proxy_name)), N'')
    SET @schedule_name            = NULLIF(LTRIM(RTRIM(@schedule_name)), N'')
    SET @target                    = NULLIF(LTRIM(RTRIM(@target)), N'')
    SET @description            = LTRIM(RTRIM(@description))

    IF (@name IS NULL)
    BEGIN
        RAISERROR(21263, -1, -1, '@name')
        RETURN (1)
    END

    -- can't have both name and uid for the schedule
    IF (@schedule_uid IS NOT NULL) AND (@schedule_name IS NOT NULL)
    BEGIN
        RAISERROR(14373, -1, -1, '@schedule_uid', '@schedule_name')
        RETURN (1)
    END

    -- Execute the check for the schedule as caller to ensure only schedules owned by caller can be attached
    EXECUTE AS CALLER;

    DECLARE @schedule_id int
    IF (@schedule_uid IS NOT NULL)
    BEGIN
        SElECT @schedule_id = schedule_id FROM sysschedules_localserver_view WHERE @schedule_uid = schedule_uid
    
        IF (@schedule_id IS NULL)
        BEGIN
            DECLARE @schedule_uid_as_char VARCHAR(36)
            SELECT @schedule_uid_as_char = CONVERT(VARCHAR(36), @schedule_uid)
            REVERT;
            RAISERROR(14262, -1, -1, N'@schedule_uid', @schedule_uid_as_char)
            RETURN (1)
        END
    END
    ELSE IF (@schedule_name IS NOT NULL)
    BEGIN
        SELECT @schedule_id = schedule_id, @schedule_uid = schedule_uid FROM sysschedules_localserver_view WHERE name = @schedule_name 
    
        IF (@schedule_id IS NULL)
        BEGIN
            REVERT;
            RAISERROR(14262, -1, -1, N'@schedule_name', @schedule_name)
            RETURN (1)
        END
    END

    REVERT;

    -- if collection_mode is cached, make sure schedule_id is not null
    IF    (@collection_mode = 0 AND @schedule_id IS NULL)
    BEGIN
        RAISERROR(14683, -1, -1)    
        RETURN (1)
    END    

    IF (@proxy_id IS NOT NULL) OR (@proxy_name IS NOT NULL) 
    BEGIN
        -- check if the proxy exists
        EXEC sp_verify_proxy_identifiers '@proxy_name',
                                         '@proxy_id',
                                         @proxy_name OUTPUT,
                                         @proxy_id   OUTPUT

        -- check if proxy_id is granted to dc_admin
        IF (@proxy_id NOT IN (SELECT proxy_id 
                              FROM sysproxylogin 
                              WHERE sid = USER_SID(USER_ID('dc_admin'))
                              )
            )
        BEGIN
            RAISERROR(14523, -1, -1, N'dc_admin', @proxy_name)
            RETURN (1)
        END
    END

    IF (@collection_mode < 0 OR @collection_mode > 1)
    BEGIN
        RAISERROR(14266, -1, -1, '@collection_mode', '0, 1')
        RETURN (1)
    END

    IF (@logging_level < 0 OR @logging_level > 2)
    BEGIN
        RAISERROR(14266, -1, -1, '@logging_level', '0, 1, or 2')
        RETURN (1)
    END

    IF (@collection_set_uid IS NULL)
    BEGIN
        SET @collection_set_uid = NEWID()
    END

    IF (@days_until_expiration < 0)
    BEGIN
        RAISERROR(14266, -1, -1, '@days_until_expiration', '>= 0')
        RETURN (1)
    END

    INSERT INTO [dbo].[syscollector_collection_sets_internal]
    (
        collection_set_uid,
        schedule_uid,
        name,
        target,
        is_running,
        proxy_id,
        is_system,
        upload_job_id,
        collection_job_id,
        collection_mode,
        logging_level,
        days_until_expiration,
        description
    )
    VALUES
    (
        @collection_set_uid,
        @schedule_uid,
        @name,
        @target,
        0,
        @proxy_id,
        0,
        NULL,
        NULL,
        @collection_mode,
        @logging_level,
        @days_until_expiration,
        @description
    )

    SET @collection_set_id = SCOPE_IDENTITY()

    IF (@collection_set_id IS NULL)
    BEGIN
        DECLARE @collection_set_id_as_char VARCHAR(36)
        SELECT @collection_set_id_as_char = CONVERT(VARCHAR(36), @collection_set_id)
        RAISERROR(14262, -1, -1, '@collection_set_id', @collection_set_id_as_char)
        RETURN (1)
    END

    IF (@TranCounter = 0)
        COMMIT TRANSACTION
    RETURN (0)

    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_create_collection_set

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);

        RETURN (1)        
    END CATCH
END
GO

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_update_job_proxy]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_update_job_proxy]...'
    DROP PROCEDURE [dbo].[sp_syscollector_update_job_proxy]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_update_job_proxy]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_update_job_proxy]
    @job_id                    uniqueidentifier,
    @proxy_id                int = NULL,
    @proxy_name                sysname = NULL
AS
BEGIN
    -- update the proxy id for the all job steps
    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_syscollector_update_proxy
    ELSE
        BEGIN TRANSACTION
    
    BEGIN TRY
        DECLARE @step_id         INT
        DECLARE cursor_job_steps CURSOR FOR
            SELECT step_id FROM dbo.sysjobsteps WHERE job_id = @job_id AND subsystem = N'CMDEXEC'

        OPEN cursor_job_steps
        FETCH NEXT FROM cursor_job_steps INTO @step_id

        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC dbo.sp_update_jobstep
                @job_id = @job_id,
                @step_id = @step_id,
                @proxy_id = @proxy_id,
                @proxy_name = @proxy_name

            FETCH NEXT FROM cursor_job_steps INTO @step_id
        END

        CLOSE      cursor_job_steps
        DEALLOCATE cursor_job_steps

        IF (@TranCounter = 0)
            COMMIT TRANSACTION
        RETURN (0)
    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_syscollector_update_proxy

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);

        RETURN (1)        
    END CATCH
END
GO

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_update_collection_set_internal]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_update_collection_set_internal]...'
    DROP PROCEDURE [dbo].[sp_syscollector_update_collection_set_internal]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_update_collection_set_internal]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_update_collection_set_internal]
    @collection_set_id          int,
    @collection_set_uid         uniqueidentifier,
    @name                       sysname,
    @new_name                   sysname,
    @target                     nvarchar(128),
    @collection_mode            smallint,         
    @days_until_expiration      smallint,
    @proxy_id                   int,              
    @proxy_name                 sysname,          
    @schedule_uid               uniqueidentifier, 
    @schedule_name              sysname,          
    @logging_level              smallint,
    @description                nvarchar(4000),   
    @schedule_id                int,
    @old_collection_mode        smallint,
    @old_proxy_id               int,
    @old_collection_job_id      uniqueidentifier,
    @old_upload_job_id          uniqueidentifier    
AS
BEGIN
    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_update_collection_set
    ELSE
        BEGIN TRANSACTION
    BEGIN TRY
        DECLARE @old_upload_schedule_id    int
        DECLARE @old_upload_schedule_uid uniqueidentifier

        SELECT  @old_upload_schedule_id = sv.schedule_id,
                @old_upload_schedule_uid = cs.schedule_uid
        FROM dbo.syscollector_collection_sets cs 
        JOIN sysschedules_localserver_view sv ON (cs.schedule_uid = sv.schedule_uid)
        WHERE collection_set_id = @collection_set_id

        -- update job names, schedule, and collection mode in a transaction to maintain a consistent state in case of failures
        IF (@collection_mode IS NOT NULL AND @collection_mode != @old_collection_mode)
        BEGIN
            IF (@schedule_id IS NULL)
            BEGIN
                -- if no schedules is supplied as a parameter to this update SP, 
                -- we can use the one that is already in the collection set table
                SET @schedule_uid = @old_upload_schedule_uid
                
                SELECT @schedule_id = schedule_id 
                FROM sysschedules_localserver_view 
                WHERE @schedule_uid = schedule_uid
            END

            IF (@schedule_name IS NOT NULL AND @schedule_name = N'')
            BEGIN
                SET @schedule_id = NULL 
            END

            -- make sure there exists a schedule we can use
            IF (@old_collection_mode = 1 AND @schedule_id IS NULL) -- a switch from non-cached to cached mode require a schedule
            BEGIN
                -- no schedules specified in input or collection set table, raise error
                RAISERROR(14683, -1, -1)
                RETURN (1)
            END

            -- Only update the jobs if we have jobs already created. Otherwise the right
            -- jobs will be created when the collection set starts for the first time.
            IF (@old_collection_job_id IS NOT NULL AND @old_upload_job_id IS NOT NULL)
            BEGIN
                -- create new jobs 
                DECLARE @collection_job_id        uniqueidentifier 
                DECLARE @upload_job_id            uniqueidentifier 

                DECLARE @collection_set_name sysname;
                SET @collection_set_name = ISNULL(@new_name, @name);
                EXEC [dbo].[sp_syscollector_create_jobs] 
                    @collection_set_id        = @collection_set_id,
                    @collection_set_uid     = @collection_set_uid,
                    @collection_set_name    = @collection_set_name,
                    @proxy_id                = @proxy_id,
                    @schedule_id            = @schedule_id,
                    @collection_mode        = @collection_mode,
                    @collection_job_id        = @collection_job_id OUTPUT,
                    @upload_job_id            = @upload_job_id OUTPUT

                UPDATE [dbo].[syscollector_collection_sets_internal]
                SET
                    upload_job_id        = @upload_job_id,
                    collection_job_id    = @collection_job_id
                WHERE @collection_set_id = collection_set_id
                
                -- drop old upload and collection jobs
                EXEC dbo.sp_syscollector_delete_jobs 
                    @collection_job_id        = @old_collection_job_id,
                    @upload_job_id            = @old_upload_job_id,
                    @schedule_id            = @old_upload_schedule_id,
                    @collection_mode        = @old_collection_mode
            END
        END
        ELSE -- collection mode unchanged, we do not have to recreate the jobs
        BEGIN
            -- we need to update the proxy id for all job steps
            IF (@old_proxy_id <> @proxy_id) OR (@old_proxy_id IS NULL AND @proxy_id IS NOT NULL)
            BEGIN
                IF (@old_collection_job_id IS NOT NULL)
                BEGIN
                    EXEC dbo.sp_syscollector_update_job_proxy
                        @job_id    = @old_collection_job_id, 
                        @proxy_id  = @proxy_id
                END

                IF (@old_upload_job_id IS NOT NULL)
                BEGIN
                    EXEC dbo.sp_syscollector_update_job_proxy
                        @job_id    = @old_upload_job_id, 
                        @proxy_id  = @proxy_id
                END
            END
            IF (@proxy_name = N'' AND @old_proxy_id IS NOT NULL) 
            BEGIN
                IF (@old_collection_job_id IS NOT NULL)
                BEGIN
                    EXEC dbo.sp_syscollector_update_job_proxy
                        @job_id    = @old_collection_job_id, 
                        @proxy_name = @proxy_name
                END

                IF (@old_upload_job_id IS NOT NULL)
                BEGIN
                    EXEC dbo.sp_syscollector_update_job_proxy
                        @job_id    = @old_upload_job_id, 
                        @proxy_name = @proxy_name
                END
            END

            -- need to update the schedule
            IF (@old_upload_schedule_id <> @schedule_id) OR (@old_upload_schedule_id IS NULL AND @schedule_id IS NOT NULL)
            BEGIN
                -- detach the old schedule 
                IF (@old_upload_job_id IS NOT NULL) AND (@old_upload_schedule_id IS NOT NULL)
                BEGIN
                    EXEC dbo.sp_detach_schedule 
                        @job_id            = @old_upload_job_id,
                        @schedule_id    = @old_upload_schedule_id,
                        @delete_unused_schedule = 0
                END

                -- attach the new schedule
                IF (@old_upload_job_id IS NOT NULL)
                BEGIN
                    EXEC dbo.sp_attach_schedule
                        @job_id            = @old_upload_job_id,
                        @schedule_id    = @schedule_id
                END
            END

            -- special case - remove the existing schedule
            IF (@schedule_name = N'') AND (@old_upload_schedule_id IS NOT NULL)
            BEGIN
                EXEC dbo.sp_detach_schedule 
                    @job_id            = @old_upload_job_id,
                    @schedule_id    = @old_upload_schedule_id,
                    @delete_unused_schedule = 0
            END
        END

        -- after the all operations succeed, update the sollection_sets table
        DECLARE @new_proxy_id int
        SET @new_proxy_id = @proxy_id
        IF (@proxy_name    = N'')    SET @new_proxy_id = NULL

        UPDATE [dbo].[syscollector_collection_sets_internal]
        SET
            name                    = ISNULL(@new_name, name),
            target                    = ISNULL(@target, target),
            proxy_id                = @new_proxy_id,
            collection_mode            = ISNULL(@collection_mode, collection_mode),
            logging_level            = ISNULL(@logging_level, logging_level),
            days_until_expiration   = ISNULL(@days_until_expiration, days_until_expiration)
        WHERE @collection_set_id = collection_set_id

        IF (@schedule_uid IS NOT NULL OR @schedule_name IS NOT NULL)
        BEGIN
            IF (@schedule_name = N'')    SET @schedule_uid = NULL

            UPDATE [dbo].[syscollector_collection_sets_internal]
            SET schedule_uid = @schedule_uid
            WHERE @collection_set_id = collection_set_id
        END

        IF (@description IS NOT NULL)
        BEGIN
            IF (@description = N'')      SET @description = NULL

            UPDATE [dbo].[syscollector_collection_sets_internal]
            SET description = @description
            WHERE @collection_set_id = collection_set_id
        END

        IF (@TranCounter = 0)
        COMMIT TRANSACTION
        RETURN (0)
    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_update_collection_set

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
        
        RETURN (1)
    END CATCH
END
GO

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_update_collection_set]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_update_collection_set]...'
    DROP PROCEDURE [dbo].[sp_syscollector_update_collection_set]
END
GO

-- Updates a collection set. These are the steps involved
-- 1- Security checks
-- 2- Stop the collection set if currently running (sp_syscollector_stop_collection_set)
-- 3- Perform the actual update transactionally (sp_syscollector_update_collection_set_internal)
-- 4- Restart the collection set if it was running (sp_syscollector_start_collection_set
PRINT 'Creating procedure [dbo].[sp_syscollector_update_collection_set]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_update_collection_set]
    @collection_set_id        int = NULL,
    @name                    sysname = NULL,
    @new_name                sysname = NULL,
    @target                    nvarchar(128) = NULL,
    @collection_mode        smallint = NULL,         -- 0: cached, 1: non-cached
    @days_until_expiration  smallint = NULL,
    @proxy_id               int = NULL,              -- mutual exclusive; must specify either proxy_id or proxy_name to identify the proxy
    @proxy_name             sysname = NULL,          -- @proxy_name = N'' is a special case to allow change of an existing proxy with NULL
    @schedule_uid           uniqueidentifier = NULL, -- mutual exclusive; must specify either schedule_uid or schedule_name to identify the schedule
    @schedule_name          sysname = NULL,          -- @schedule_name = N'' is a special case to allow change of an existing schedule with NULL
    @logging_level            smallint = NULL,
    @description            nvarchar(4000) = NULL   -- @description = N'' is a special case to allow change of an existing description with NULL
WITH EXECUTE AS OWNER -- 'MS_DataCollectorInternalUser'
AS
BEGIN
    -- Security checks will be performed against caller's security context
    EXECUTE AS CALLER;

    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_operator'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        REVERT;
        RAISERROR(14677, -1, -1, 'dc_operator')
        RETURN (1)
    END

    -- Security checks (restrict functionality for non-dc_admin-s)
    IF (((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1)) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        AND (@new_name IS NOT NULL))
    BEGIN
        REVERT;
        RAISERROR(14676, -1, -1, '@new_name', 'dc_admin')
        RETURN (1)
    END

    IF (((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1)) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        AND (@target IS NOT NULL))
    BEGIN
        REVERT;
        RAISERROR(14676, -1, -1, '@target', 'dc_admin')
        RETURN (1)
    END

    IF (((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1)) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        AND (@proxy_id IS NOT NULL))
    BEGIN
        REVERT;
        RAISERROR(14676, -1, -1, '@proxy_id', 'dc_admin')
        RETURN (1)
    END

    IF (((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1)) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        AND (@collection_mode IS NOT NULL))
    BEGIN
        REVERT;
        RAISERROR(14676, -1, -1, '@collection_mode', 'dc_admin')
        RETURN (1)
    END

    IF (((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1)) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        AND (@description IS NOT NULL))
    BEGIN
        REVERT;
        RAISERROR(14676, -1, -1, '@description', 'dc_admin')
        RETURN (1)
    END

    IF (((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1)) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        AND (@days_until_expiration IS NOT NULL))
    BEGIN
        REVERT;
        RAISERROR(14676, -1, -1, '@days_until_expiration', 'dc_admin')
        RETURN (1) -- Failure
    END

    -- Security checks done, reverting now to internal data collector user security context
    REVERT;

    -- check for inconsistencies/errors in the parameters
    DECLARE @retVal int
    EXEC @retVal = dbo.sp_syscollector_verify_collection_set @collection_set_id OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (1)

    IF (@collection_mode IS NOT NULL AND (@collection_mode < 0 OR @collection_mode > 1))
    BEGIN
        RAISERROR(14266, -1, -1, '@collection_mode', '0, 1')
        RETURN (1)
    END

    IF (@logging_level IS NOT NULL AND (@logging_level < 0 OR @logging_level > 2))
    BEGIN
        RAISERROR(14266, -1, -1, '@logging_level', '0, 1, or 2')
        RETURN(1)
    END

    IF (LEN(@new_name) = 0)
    BEGIN
      RAISERROR(21263, -1, -1, '@new_name')
      RETURN(1) -- Failure
    END    

    -- Remove any leading/trailing spaces from parameters
    SET @target                    = NULLIF(LTRIM(RTRIM(@target)), N'')
    SET @new_name                = NULLIF(LTRIM(RTRIM(@new_name)), N'')
    SET @description            = LTRIM(RTRIM(@description))
    
    DECLARE @is_system                    bit
    DECLARE @is_running                    bit
    DECLARE @collection_set_uid            uniqueidentifier
    DECLARE @old_collection_mode        smallint
    DECLARE @old_upload_job_id            uniqueidentifier
    DECLARE @old_collection_job_id        uniqueidentifier
    DECLARE @old_proxy_id                int

    SELECT    @is_running = is_running,
            @is_system = is_system,
            @collection_set_uid = collection_set_uid,
            @old_collection_mode = collection_mode,
            @old_collection_job_id = collection_job_id, 
            @old_upload_job_id = upload_job_id,
            @old_proxy_id = proxy_id
    FROM dbo.syscollector_collection_sets
    WHERE collection_set_id = @collection_set_id

    IF (@is_system = 1 AND (
            @new_name IS NOT NULL OR 
            @description IS NOT NULL))
    BEGIN
        -- cannot update, delete, or add new collection items to a system collection set
        RAISERROR(14696, -1, -1);
        RETURN (1)
    END
    
    IF (@proxy_id IS NOT NULL) OR (@proxy_name IS NOT NULL AND @proxy_name <> N'')
    BEGIN
        -- verify the proxy exists
        EXEC sp_verify_proxy_identifiers '@proxy_name',
                                         '@proxy_id',
                                         @proxy_name OUTPUT,
                                         @proxy_id   OUTPUT

        -- check if proxy_id is granted to dc_admin
        IF (@proxy_id NOT IN (SELECT proxy_id 
                              FROM sysproxylogin 
                              WHERE sid = USER_SID(USER_ID('dc_admin'))
                              )
            )
        BEGIN
            RAISERROR(14523, -1, -1, N'dc_admin', @proxy_name)
            RETURN (1)
        END
    END
    ELSE -- if no proxy_id provided, get the existing proxy_id, might need it later to create new jobs
    BEGIN
        SET @proxy_id = @old_proxy_id
    END

    -- can't have both uid and name passed for the schedule
    IF (@schedule_uid IS NOT NULL) AND (@schedule_name IS NOT NULL AND @schedule_name <> N'')
    BEGIN
        RAISERROR(14373, -1, -1, '@schedule_uid', '@schedule_name')
        RETURN (1)
    END

    -- check if it attempts to remove a schedule when the collection mode is cached
    IF    (@schedule_name = N'' AND @collection_mode = 0)    OR 
        (@collection_mode IS NULL AND @old_collection_mode = 0 AND @schedule_name = N'')
    BEGIN
        RAISERROR(14683, -1, -1)    
        RETURN (1)
    END    

    -- Execute the check for the schedule as caller to ensure only schedules owned by caller can be attached
    EXECUTE AS CALLER;

    DECLARE @schedule_id int
    SET @schedule_id = NULL
    IF (@schedule_uid IS NOT NULL)
    BEGIN
        SElECT @schedule_id = schedule_id FROM sysschedules_localserver_view WHERE @schedule_uid = schedule_uid
    
        IF (@schedule_id IS NULL)
        BEGIN
            DECLARE @schedule_uid_as_char VARCHAR(36)
            SELECT @schedule_uid_as_char = CONVERT(VARCHAR(36), @schedule_uid)
            REVERT;
            RAISERROR(14262, -1, -1, N'@schedule_uid', @schedule_uid_as_char)
            RETURN (1)
        END
    END
    ELSE IF (@schedule_name IS NOT NULL AND @schedule_name <> N'') -- @schedule_name is not null
    BEGIN
        SELECT @schedule_id = schedule_id, @schedule_uid = schedule_uid FROM sysschedules_localserver_view WHERE name = @schedule_name 
    
        IF (@schedule_id IS NULL)
        BEGIN
            REVERT;
            RAISERROR(14262, -1, -1, N'@schedule_name', @schedule_name)
            RETURN (1)
        END
    END

    REVERT;
    
    -- Stop the collection set if it is currently running
    IF (@is_running = 1 AND (
            @new_name IS NOT NULL OR 
            @target IS NOT NULL OR 
            @proxy_id IS NOT NULL OR 
            @logging_level IS NOT NULL OR 
            @collection_mode IS NOT NULL))
    BEGIN
        EXEC @retVal = sp_syscollector_stop_collection_set @collection_set_id = @collection_set_id
        IF (@retVal <> 0)
            RETURN (1)
    END

    -- Passed all necessary checks, go ahead with the update
    EXEC @retVal = sp_syscollector_update_collection_set_internal
        @collection_set_id = @collection_set_id,
        @collection_set_uid = @collection_set_uid,
        @name = @name,
        @new_name = @new_name,
        @target = @target,
        @collection_mode = @collection_mode,
        @days_until_expiration = @days_until_expiration,
        @proxy_id = @proxy_id,
        @proxy_name = @proxy_name,
        @schedule_uid = @schedule_uid,
        @schedule_name = @schedule_name,
        @logging_level = @logging_level,
        @description = @description,
        @schedule_id = @schedule_id,
        @old_collection_mode = @old_collection_mode,
        @old_proxy_id = @old_proxy_id,
        @old_collection_job_id = @old_collection_job_id,
        @old_upload_job_id = @old_upload_job_id
            
     IF (@retVal <> 0)
        RETURN (1)
        
     -- Restart the collection set if it has been already running
     IF (@is_running = 1)
     BEGIN
         EXEC @retVal = sp_syscollector_start_collection_set
                            @collection_set_id = @collection_set_id
         IF (@retVal <> 0)
            RETURN (1)
     END
        
     RETURN (0)
END
GO

IF (NOT OBJECT_ID('dbo.sp_syscollector_configure_sql_dumper', 'P') IS NULL)
BEGIN
    DROP PROCEDURE [dbo].[sp_syscollector_configure_sql_dumper]
END
GO

PRINT ''
PRINT 'Creating procedure [dbo].[sp_syscollector_configure_sql_dumper]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_configure_sql_dumper]
    @collection_set_id        int = NULL,
    @name                    sysname = NULL,
    @dump_on_any_error      bit = NULL,                -- configure SQL dumper to dump on any SSIS errors
    @dump_on_codes          nvarchar(max) = NULL    -- configure SQL dumper to dump when we hit one of the specified SSIS errors. Set to N'' to remove the codes.
AS
BEGIN
    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_admin')
        RETURN(1) -- Failure
    END

    DECLARE @retVal int
    EXEC @retVal = dbo.sp_syscollector_verify_collection_set @collection_set_id OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (1)

    DECLARE @is_running bit
    SELECT    @is_running = is_running
    FROM dbo.syscollector_collection_sets
    WHERE collection_set_id = @collection_set_id
    IF (@is_running = 1)
    BEGIN
        RAISERROR(14711, 0, 1)
    END

    IF (@dump_on_codes = N'')
    BEGIN
        UPDATE [dbo].[syscollector_collection_sets_internal]
        SET dump_on_codes = NULL
        WHERE @collection_set_id = collection_set_id
    END
    ELSE IF (@dump_on_codes IS NOT NULL)
    BEGIN
        UPDATE [msdb].[dbo].[syscollector_collection_sets_internal]
        SET dump_on_codes = @dump_on_codes
        WHERE @collection_set_id = collection_set_id
    END    

    IF (@dump_on_any_error IS NOT NULL)
    BEGIN
        UPDATE [msdb].[dbo].[syscollector_collection_sets_internal]
        SET dump_on_any_error = @dump_on_any_error
        WHERE @collection_set_id = collection_set_id
    END

    RETURN (0)
END
GO

---------------------------------------------------------------
-- Collector type
---------------------------------------------------------------
IF (OBJECT_ID(N'[dbo].[syscollector_collector_types_internal]', 'U') IS NULL)
BEGIN
    PRINT 'Creating table [dbo].[syscollector_collector_types_internal]...'
    CREATE TABLE [dbo].[syscollector_collector_types_internal] (
        collector_type_uid                uniqueidentifier NOT NULL,
        name                            sysname NOT NULL,
        parameter_schema                xml NULL,
        parameter_formatter                xml NULL,
        schema_collection                sysname NULL,
        collection_package_name            sysname NOT NULL,
        collection_package_folderid        uniqueidentifier NOT NULL,
        upload_package_name                sysname NOT NULL,
        upload_package_folderid            uniqueidentifier NOT NULL,
        is_system                        bit default 0 NOT NULL,
        CONSTRAINT [PK_syscollector_collector_types_internal] PRIMARY KEY CLUSTERED (collector_type_uid ASC),
        CONSTRAINT [UQ_syscollector_collection_types_internal_name] UNIQUE (name)
        )
    ALTER TABLE syscollector_collector_types_internal
        ADD CONSTRAINT [FK_syscollector_collector_types_internal_upload_sysssispackages] FOREIGN KEY(upload_package_folderid, upload_package_name)
        REFERENCES sysssispackages (folderid, [name])
    ALTER TABLE syscollector_collector_types_internal
        ADD CONSTRAINT [FK_syscollector_collector_types_internal_collection_sysssispackages] FOREIGN KEY(collection_package_folderid, collection_package_name)
        REFERENCES sysssispackages (folderid, [name])
END
GO

-- [fn_syscollector_get_package_path]
-- This function returns the full path of a SSIS package given the package id
IF (NOT OBJECT_ID('[dbo].[fn_syscollector_get_package_path]', 'FN') IS NULL)
BEGIN
    PRINT 'Dropping function [dbo].[fn_syscollector_get_package_path]...'
    DROP FUNCTION [dbo].[fn_syscollector_get_package_path]
END
GO

PRINT 'Creating function [dbo].[fn_syscollector_get_package_path]...'
GO
CREATE FUNCTION [dbo].[fn_syscollector_get_package_path] 
(
    @package_id uniqueidentifier
)
RETURNS NVARCHAR(4000)
AS
BEGIN
    IF @package_id IS NULL
        RETURN NULL

    DECLARE @package_path nvarchar(4000)
    DECLARE @prevfolderid uniqueidentifier
    DECLARE @folderid uniqueidentifier
    DECLARE @package_name sysname
    SET @package_path = ''

    SELECT @package_name = name, 
            @folderid = folderid 
    FROM dbo.sysssispackages
    WHERE id = @package_id

    WHILE (@folderid != '00000000-0000-0000-0000-000000000000')
    BEGIN
        SET @prevfolderid = @folderid

        DECLARE @foldername sysname
        SELECT @foldername = foldername, 
                @folderid = parentfolderid 
        FROM dbo.sysssispackagefolders
        WHERE folderid = @prevfolderid
        SET @package_path = @foldername + N'\\' + @package_path
    END

    SET @package_path = N'\\' + @package_path + @package_name
    RETURN @package_path
END
GO

IF (NOT OBJECT_ID(N'[dbo].[syscollector_collector_types]', 'V') IS NULL)
BEGIN
    PRINT 'Dropping view [dbo].[syscollector_collector_types]...'
    DROP VIEW [dbo].[syscollector_collector_types]
END
GO

PRINT 'Creating view [dbo].[syscollector_collector_types]...'
GO
CREATE VIEW [dbo].[syscollector_collector_types]
AS
    SELECT 
        t.collector_type_uid,
        t.name,
        t.parameter_schema,
        t.parameter_formatter,
        s1.id AS collection_package_id,
        dbo.fn_syscollector_get_package_path(s1.id) AS collection_package_path,
        s1.name AS collection_package_name,
        s2.id AS upload_package_id,
        dbo.fn_syscollector_get_package_path(s2.id) AS upload_package_path,
        s2.name AS upload_package_name,
        t.is_system
    FROM 
        [dbo].[syscollector_collector_types_internal] AS t,
        sysssispackages s1,
        sysssispackages s2
    WHERE t.collection_package_folderid = s1.folderid
      AND t.collection_package_name = s1.name
      AND t.upload_package_folderid = s2.folderid
      AND t.upload_package_name = s2.name
GO

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_verify_collector_type]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_verify_collector_type]...'
    DROP PROCEDURE [dbo].[sp_syscollector_verify_collector_type]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_verify_collector_type]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_verify_collector_type]
    @collector_type_uid        uniqueidentifier = NULL OUTPUT,
    @name                    sysname = NULL OUTPUT
AS
BEGIN
    IF (@name IS NOT NULL)
    BEGIN
        -- Remove any leading/trailing spaces from parameters
        SET @name                    = NULLIF(LTRIM(RTRIM(@name)), N'')
    END

    IF (@collector_type_uid IS NULL AND @name IS NULL)
    BEGIN
        RAISERROR(14624, -1, -1, '@collector_type_uid, @name')
        RETURN(1)
    END

    IF (@collector_type_uid IS NOT NULL AND @name IS NOT NULL)
    BEGIN
        IF (NOT EXISTS(SELECT *
                        FROM dbo.syscollector_collector_types
                        WHERE collector_type_uid = @collector_type_uid
                        AND name = @name))
        BEGIN
            DECLARE @errMsg NVARCHAR(196)
            SELECT @errMsg = CONVERT(NVARCHAR(36), @collector_type_uid) + ', ' + @name
            RAISERROR(14262, -1, -1, '@collector_type_uid, @name', @errMsg)
            RETURN(1)
        END
    END
    -- Check id
    ELSE IF (@collector_type_uid IS NOT NULL)
    BEGIN
        SELECT @name = name
        FROM dbo.syscollector_collector_types
        WHERE (collector_type_uid = @collector_type_uid)
    
        -- the view would take care of all the permissions issues.
        IF (@name IS NULL) 
        BEGIN
            DECLARE @collector_type_uid_as_char VARCHAR(36)
            SELECT @collector_type_uid_as_char = CONVERT(VARCHAR(36), @collector_type_uid)
            RAISERROR(14262, -1, -1, '@collector_type_uid', @collector_type_uid_as_char)
            RETURN(1) -- Failure
        END
    END
    -- Check name
    ELSE IF (@name IS NOT NULL)
    BEGIN
        -- get the corresponding collector_type_uid (if the collector type exists)
        SELECT @collector_type_uid = collector_type_uid
        FROM dbo.syscollector_collector_types
        WHERE (name = @name)

        -- the view would take care of all the permissions issues.
        IF (@collector_type_uid IS NULL)
        BEGIN
            RAISERROR(14262, -1, -1, '@name', @name)
            RETURN(1) -- Failure
        END
    END
    RETURN (0)
END
GO

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_create_collector_type]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_create_collector_type]...'
    DROP PROCEDURE [dbo].[sp_syscollector_create_collector_type]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_create_collector_type]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_create_collector_type]
    @collector_type_uid            uniqueidentifier = NULL OUTPUT,
    @name                        sysname,
    @parameter_schema            xml = NULL,
    @parameter_formatter        xml = NULL,
    @collection_package_id        uniqueidentifier,
    @upload_package_id            uniqueidentifier
AS
BEGIN
    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_create_collector_type
    ELSE
        BEGIN TRANSACTION
    BEGIN TRY

    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_admin')
        RETURN (1)
    END

    SET @name                = NULLIF(LTRIM(RTRIM(@name)), N'')
    IF (@name IS NULL) 
    BEGIN
        RAISERROR(21263, -1, -1, '@name', @name)
        RETURN (1)
    END

    IF (@collector_type_uid IS NULL) 
    BEGIN
        SET @collector_type_uid = NEWID()
    END
    
    IF (NOT EXISTS(SELECT * from sysssispackages
        WHERE @collection_package_id = id))
    BEGIN
        DECLARE @collection_package_id_as_char VARCHAR(36)
        SELECT @collection_package_id_as_char = CONVERT(VARCHAR(36), @collection_package_id)
        RAISERROR(14262, -1, -1, '@collection_package_id', @collection_package_id_as_char)
        RETURN (1)
    END

    IF (NOT EXISTS(SELECT * from sysssispackages
        WHERE @upload_package_id = id))
    BEGIN
        DECLARE @upload_package_id_as_char VARCHAR(36)
        SELECT @upload_package_id_as_char = CONVERT(VARCHAR(36), @upload_package_id)
        RAISERROR(14262, -1, -1, '@upload_package_id', @upload_package_id_as_char)
        RETURN (1)
    END

    DECLARE @collection_package_name sysname
    DECLARE @collection_package_folderid uniqueidentifier
    DECLARE @upload_package_name sysname
    DECLARE @upload_package_folderid uniqueidentifier    

    SELECT 
        @collection_package_name = name,
        @collection_package_folderid = folderid
    FROM sysssispackages
    WHERE @collection_package_id = id

    SELECT 
        @upload_package_name = name,
        @upload_package_folderid = folderid
    FROM sysssispackages
    WHERE @upload_package_id = id

    DECLARE @schema_collection sysname
    IF (@parameter_schema IS NOT NULL)
    BEGIN
        SET @schema_collection = N'schema_collection_' + @name
        WHILE (EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = @schema_collection))
        BEGIN
            SET @schema_collection = LEFT(@schema_collection, 119) + '_' + RIGHT(STR(FLOOR(RAND() * 100000000)),8)
        END

        DECLARE @retVal int
        DECLARE @sql_string nvarchar(2048)
        DECLARE @param_definition nvarchar(16)
        SET @param_definition = N'@schema xml'
        SET @sql_string = N'CREATE XML SCHEMA COLLECTION ' + QUOTENAME(@schema_collection, '[') + N' AS @schema; '
        SET @sql_string = @sql_string + N'GRANT EXECUTE ON XML SCHEMA COLLECTION::[dbo].' + QUOTENAME(@schema_collection, '[') + N' TO dc_admin; ' 
        SET @sql_string = @sql_string + N'GRANT VIEW DEFINITION ON XML SCHEMA COLLECTION::[dbo].' + QUOTENAME(@schema_collection, '[') + N' TO dc_admin; '

        EXEC sp_executesql @sql_string, @param_definition, @schema = @parameter_schema
    END

    INSERT INTO [dbo].[syscollector_collector_types_internal]
    (
        collector_type_uid,
        name,
        parameter_schema,
        parameter_formatter,
        schema_collection,
        collection_package_name,
        collection_package_folderid,
        upload_package_name,
        upload_package_folderid
    )
    VALUES
    (
        @collector_type_uid,
        @name,
        @parameter_schema,
        @parameter_formatter,
        @schema_collection,
        @collection_package_name,
        @collection_package_folderid,
        @upload_package_name,
        @upload_package_folderid
    )

    IF (@TranCounter = 0)
        COMMIT TRANSACTION
    RETURN (0)
    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_create_collector_type

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);

        RETURN (1)    
    END CATCH
END
GO

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_update_collector_type]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_update_collector_type]...'
    DROP PROCEDURE [dbo].[sp_syscollector_update_collector_type]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_update_collector_type]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_update_collector_type]
    @collector_type_uid            uniqueidentifier = NULL,
    @name                        sysname = NULL,
    @parameter_schema            xml = NULL,
    @parameter_formatter        xml = NULL,
    @collection_package_id        uniqueidentifier,
    @upload_package_id            uniqueidentifier
AS
BEGIN
    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_update_collector_type
    ELSE
        BEGIN TRANSACTION
    BEGIN TRY

    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_admin')
        RETURN (1)
    END

    -- Check the validity of the name/uid pair
    DECLARE @retVal int
    EXEC @retVal = dbo.sp_syscollector_verify_collector_type @collector_type_uid OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (1)
    
    DECLARE @old_parameter_schema       xml
    DECLARE @old_parameter_formatter    xml
    DECLARE @old_collection_package_id  uniqueidentifier
    DECLARE @old_upload_package_id      uniqueidentifier

    SELECT  @old_parameter_schema = parameter_schema,
            @old_parameter_formatter = parameter_formatter,
            @old_collection_package_id = collection_package_id,
            @old_upload_package_id = upload_package_id
    FROM [dbo].[syscollector_collector_types]
    WHERE name = @name
    AND collector_type_uid = @collector_type_uid

    IF (@collection_package_id IS NULL)
    BEGIN
        SET @collection_package_id = @old_collection_package_id
    END
    ELSE IF (NOT EXISTS(SELECT * from sysssispackages
                        WHERE @collection_package_id = id))
    BEGIN
        DECLARE @collection_package_id_as_char VARCHAR(36)
        SELECT @collection_package_id_as_char = CONVERT(VARCHAR(36), @collection_package_id)
        RAISERROR(14262, -1, -1, '@collection_package_id', @collection_package_id_as_char)
        RETURN (1)
    END

    IF (@upload_package_id IS NULL)
    BEGIN
        SET @upload_package_id = @old_upload_package_id
    END
    ELSE IF (NOT EXISTS(SELECT * from sysssispackages
                        WHERE @upload_package_id = id))
    BEGIN
        DECLARE @upload_package_id_as_char VARCHAR(36)
        SELECT @upload_package_id_as_char = CONVERT(VARCHAR(36), @upload_package_id)
        RAISERROR(14262, -1, -1, '@upload_package_id', @upload_package_id_as_char)
        RETURN (1)
    END

    DECLARE @collection_package_name sysname
    DECLARE @collection_package_folderid uniqueidentifier
    DECLARE @upload_package_name sysname
    DECLARE @upload_package_folderid uniqueidentifier    

    SELECT 
        @collection_package_name = name,
        @collection_package_folderid = folderid
    FROM sysssispackages
    WHERE @collection_package_id = id

    SELECT 
        @upload_package_name = name,
        @upload_package_folderid = folderid
    FROM sysssispackages
    WHERE @upload_package_id = id

    DECLARE @schema_collection sysname
    IF (@parameter_schema IS NULL)
    BEGIN
        SET @parameter_schema = @old_parameter_schema
    END
    ELSE
    BEGIN
        SELECT @schema_collection = schema_collection
        FROM [dbo].[syscollector_collector_types_internal]
        WHERE name = @name
        AND collector_type_uid = @collector_type_uid

        -- if a previous xml schema collection existed with the same name, drop it in favor of the new schema
        IF (EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = @schema_collection))
        BEGIN
            DECLARE @sql_drop_schema nvarchar(512)
            SET @sql_drop_schema = N'DROP XML SCHEMA COLLECTION ' + QUOTENAME(@schema_collection)
            EXECUTE sp_executesql @sql_drop_schema
        END

        -- recreate it with the new schema
        DECLARE @sql_create_schema nvarchar(2048)
        DECLARE @param_definition nvarchar(16)
        SET @param_definition = N'@schema xml'
        SET @sql_create_schema = N'CREATE XML SCHEMA COLLECTION ' + QUOTENAME(@schema_collection) + N' AS @schema; '
        SET @sql_create_schema = @sql_create_schema + N'GRANT EXECUTE ON XML SCHEMA COLLECTION::[dbo].' + QUOTENAME(@schema_collection) + N' TO dc_admin; ' 
        SET @sql_create_schema = @sql_create_schema + N'GRANT VIEW DEFINITION ON XML SCHEMA COLLECTION::[dbo].' + QUOTENAME(@schema_collection) + N' TO dc_admin; '
            
        EXEC sp_executesql @sql_create_schema, @param_definition, @schema = @parameter_schema
    END

    UPDATE [dbo].[syscollector_collector_types_internal]
    SET parameter_schema = @parameter_schema,
        parameter_formatter = @parameter_formatter,
        schema_collection = @schema_collection,
        collection_package_name = @collection_package_name,
        collection_package_folderid = @collection_package_folderid,
        upload_package_name = @upload_package_name,
        upload_package_folderid = @upload_package_folderid
    WHERE @collector_type_uid = collector_type_uid
    AND   @name = name

    IF (@TranCounter = 0)
        COMMIT TRANSACTION
    RETURN (0)
    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_update_collector_type

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);

        RETURN (1)    
    END CATCH
END
GO



IF (NOT OBJECT_ID('[dbo].[sp_syscollector_validate_xml]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_validate_xml]...'
    DROP PROCEDURE [dbo].[sp_syscollector_validate_xml]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_validate_xml]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_validate_xml]
    @collector_type_uid        uniqueidentifier = NULL,
    @name                    sysname = NULL,
    @parameters                xml
AS
BEGIN
    DECLARE @retVal int
    EXEC @retVal = dbo.sp_syscollector_verify_collector_type @collector_type_uid OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (1)

    DECLARE @schema_collection sysname
    SET @schema_collection = (SELECT schema_collection 
                                FROM syscollector_collector_types_internal
                                WHERE @collector_type_uid = collector_type_uid)

    IF (@schema_collection IS NULL)
    BEGIN
        RAISERROR(21263, -1, -1, '@schema_collection')
        RETURN (1)
    END

        -- @sql_string should have enough buffer to include 2n+2 max size for QUOTENAME(@schema_collection).
    DECLARE @sql_string nvarchar(328)
    DECLARE @param_definition nvarchar(16)
    SET @param_definition = N'@param xml'
    SET @sql_string = N'DECLARE @validated_xml XML (DOCUMENT ' + QUOTENAME(@schema_collection, '[') + N'); SELECT @validated_xml = @param';
    EXEC @retVal = sp_executesql @sql_string, @param_definition, @param = @parameters

    IF (@retVal <> 0)
    BEGIN
        RETURN (1)
    END
END
GO

---------------------------------------------------------------
-- Collection item
---------------------------------------------------------------
IF (OBJECT_ID(N'[dbo].[syscollector_collection_items_internal]', 'U') IS NULL)
BEGIN
    PRINT 'Creating table [dbo].[syscollector_collection_items_internal]...'
    CREATE TABLE [dbo].[syscollector_collection_items_internal] (
        collection_set_id            int NOT NULL,
        collection_item_id            int IDENTITY NOT NULL,
        collector_type_uid            uniqueidentifier NOT NULL,
        name                        sysname NOT NULL,
        name_id                        int NULL,                        -- sysmessage id of the name of the item (for localizing system collection set)
        frequency                    int NOT NULL,
        parameters                    xml NULL,
        CONSTRAINT [PK_syscollector_collection_items_internal] PRIMARY KEY CLUSTERED (collection_set_id ASC, collection_item_id ASC),
        CONSTRAINT [UQ_syscollector_collection_items_internal_name] UNIQUE (name)
        )
    ALTER TABLE syscollector_collection_items_internal
        ADD CONSTRAINT [FK_syscollector_collection_items_internal_syscollector_collection_sets_internal] FOREIGN KEY(collection_set_id)
        REFERENCES syscollector_collection_sets_internal (collection_set_id) ON DELETE CASCADE
    ALTER TABLE syscollector_collection_items_internal
        ADD CONSTRAINT [FK_syscollector_collection_items_internal_syscollector_collector_types_internal] FOREIGN KEY(collector_type_uid)
        REFERENCES syscollector_collector_types_internal (collector_type_uid) ON DELETE CASCADE
END ELSE BEGIN   
    IF (NOT EXISTS (SELECT * FROM msdb.dbo.syscolumns WHERE name = N'name_id' AND id = OBJECT_ID(N'[dbo].[syscollector_collection_items_internal]')))
    BEGIN
        PRINT 'Altering table [dbo].[syscollector_collection_items_internal]...'
        ALTER TABLE [dbo].[syscollector_collection_items_internal] ADD name_id int NULL
    END    
END
GO

IF (NOT OBJECT_ID(N'[dbo].[syscollector_collection_items]', 'V') IS NULL)
BEGIN
    PRINT 'Dropping view [dbo].[syscollector_collection_items]...'
    DROP VIEW [dbo].[syscollector_collection_items]
END
GO

PRINT 'Creating view [dbo].[syscollector_collection_items]...'
GO
CREATE VIEW [dbo].[syscollector_collection_items]
AS
    SELECT
        i.collection_set_id,
        i.collection_item_id,
        i.collector_type_uid,
        CASE 
            WHEN i.name_id IS NULL THEN i.name 
            ELSE FORMATMESSAGE(i.name_id)
        END AS name,        
        i.frequency,
        i.parameters
    FROM 
        [dbo].[syscollector_collection_items_internal] i
GO

-- This is a stored procedure of collector_type, but it is created here because it 
-- makes references to the collection item view
IF (NOT OBJECT_ID('[dbo].[sp_syscollector_delete_collector_type]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_delete_collector_type]...'
    DROP PROCEDURE [dbo].[sp_syscollector_delete_collector_type]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_delete_collector_type]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_delete_collector_type]
    @collector_type_uid            uniqueidentifier = NULL,
    @name                        sysname = NULL
AS
BEGIN
    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_delete_collector_type
    ELSE
        BEGIN TRANSACTION
    BEGIN TRY

    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_admin')
        RETURN (1)
    END
    
    DECLARE @retVal int
    EXEC @retVal = dbo.sp_syscollector_verify_collector_type @collector_type_uid OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (1)

    IF (EXISTS(SELECT * from dbo.syscollector_collection_items
        WHERE @collector_type_uid = collector_type_uid))
    BEGIN
        RAISERROR(14673, -1, -1, @name)
        RETURN (1)
    END
    
    DECLARE @schema_collection sysname
        -- @sql_string should have enough buffer to include 2n+2 max size for QUOTENAME(@schema_collection).
    DECLARE @sql_string nvarchar(285)
    SET @schema_collection = (SELECT schema_collection 
                                FROM syscollector_collector_types_internal
                                WHERE @collector_type_uid = collector_type_uid)
    SET @sql_string = N'DROP XML SCHEMA COLLECTION ' + QUOTENAME(@schema_collection, '[');
    EXEC sp_executesql @sql_string

    DELETE [dbo].[syscollector_collector_types_internal]
    WHERE collector_type_uid = @collector_type_uid

    IF (@TranCounter = 0)
        COMMIT TRANSACTION
    RETURN (0)
    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_delete_collector_type

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
        
        RETURN (1)
    END CATCH
END
GO

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_verify_collection_item]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_verify_collection_item]...'
    DROP PROCEDURE [dbo].[sp_syscollector_verify_collection_item]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_verify_collection_item]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_verify_collection_item]
    @collection_item_id        int = NULL OUTPUT,
    @name                    sysname = NULL OUTPUT
AS
BEGIN
    IF (@name IS NOT NULL)
    BEGIN
        -- Remove any leading/trailing spaces from parameters
        SET @name                    = NULLIF(LTRIM(RTRIM(@name)), N'')
    END

    IF (@collection_item_id IS NULL AND @name IS NULL)
    BEGIN
        RAISERROR(14624, -1, -1, '@collection_item_id, @name')
        RETURN(1)
    END

    IF (@collection_item_id IS NOT NULL AND @name IS NOT NULL)
    BEGIN
        IF (NOT EXISTS(SELECT *
                        FROM dbo.syscollector_collection_items
                        WHERE collection_item_id = @collection_item_id
                        AND name = @name))
        BEGIN
            DECLARE @errMsg NVARCHAR(196)
            SELECT @errMsg = CONVERT(NVARCHAR(36), @collection_item_id) + ', ' + @name
            RAISERROR(14262, -1, -1, '@collection_item_id, @name', @errMsg)
            RETURN(1)
        END
    END
    -- Check id
    ELSE IF (@collection_item_id IS NOT NULL)
    BEGIN
        SELECT @name = name
        FROM dbo.syscollector_collection_items
        WHERE (collection_item_id = @collection_item_id)
    
        -- the view would take care of all the permissions issues.
        IF (@name IS NULL) 
        BEGIN
            DECLARE @collection_item_id_as_char VARCHAR(36)
            SELECT @collection_item_id_as_char = CONVERT(VARCHAR(36), @collection_item_id)
            RAISERROR(14262, -1, -1, '@collection_item_id', @collection_item_id_as_char)
            RETURN(1) -- Failure
        END
    END
    -- Check name
    ELSE IF (@name IS NOT NULL)
    BEGIN
        -- get the corresponding collection_item_id (if the collection_item exists)
        SELECT @collection_item_id = collection_item_id
        FROM dbo.syscollector_collection_items
        WHERE (name = @name)

        -- the view would take care of all the permissions issues.
        IF (@collection_item_id IS NULL)
        BEGIN
            RAISERROR(14262, -1, -1, '@name', @name)
            RETURN(1) -- Failure
        END
    END
    RETURN (0)
END
GO

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_create_collection_item]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_create_collection_item]...'
    DROP PROCEDURE [dbo].[sp_syscollector_create_collection_item]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_create_collection_item]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_create_collection_item]
    @collection_set_id        int,
    @collector_type_uid        uniqueidentifier,
    @name                    sysname,
    @frequency                int = 5,                -- set by default to the minimum frequency
    @parameters                xml = NULL,
    @collection_item_id        int OUTPUT
AS
BEGIN
    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_create_collection_item
    ELSE
        BEGIN TRANSACTION
    BEGIN TRY
        -- Security check (role membership)
        IF (NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        BEGIN
            RAISERROR(14677, -1, -1, 'dc_admin')
            RETURN (1)
        END

        DECLARE @is_system bit
        SELECT    @is_system = is_system
        FROM dbo.syscollector_collection_sets
        WHERE collection_set_id = @collection_set_id
        
        IF (@is_system = 1)
        BEGIN
            -- cannot update, delete, or add new collection items to a system collection set
            RAISERROR(14696, -1, -1);
            RETURN (1)
        END

        SET @name            = NULLIF(LTRIM(RTRIM(@name)), N'')
        IF (@name IS NULL) 
        BEGIN
            RAISERROR(21263, -1, -1, '@name')
            RETURN (1)
        END
        
        IF (@frequency < 5)
        BEGIN
            DECLARE @frequency_as_char VARCHAR(36)
            SELECT @frequency_as_char = CONVERT(VARCHAR(36), @frequency)
            RAISERROR(21405, 16, -1, @frequency_as_char, '@frequency', 5)
            RETURN (1)
        END

        IF (NOT EXISTS(SELECT * from dbo.syscollector_collector_types
            WHERE @collector_type_uid = collector_type_uid))
        BEGIN
            DECLARE @collector_type_uid_as_char VARCHAR(36)
            SELECT @collector_type_uid_as_char = CONVERT(VARCHAR(36), @collector_type_uid)
            RAISERROR(14262, -1, -1, '@collector_type_uid', @collector_type_uid_as_char)
            RETURN (1)
        END
        
        IF (NOT EXISTS(SELECT * from dbo.syscollector_collection_sets
            WHERE @collection_set_id = collection_set_id))
        BEGIN
            DECLARE @collection_set_id_as_char VARCHAR(36)
            SELECT @collection_set_id_as_char = CONVERT(VARCHAR(36), @collection_set_id)
            RAISERROR(14262, -1, -1, '@collection_set_id', @collection_set_id_as_char)
            RETURN (1)
        END

        DECLARE @is_running bit
        SELECT    @is_running = is_running
        FROM dbo.syscollector_collection_sets
        WHERE collection_set_id = @collection_set_id
        IF (@is_running = 1)
        BEGIN
            RAISERROR(14675, -1, -1, @name)
            RETURN (1)
        END

        IF (@parameters IS NULL)
        BEGIN
            DECLARE @parameter_schema xml
            SELECT @parameter_schema = parameter_schema FROM syscollector_collector_types WHERE collector_type_uid = @collector_type_uid
            IF (@parameter_schema IS NOT NULL)    -- only allows parameters to be null if the collector type has a null schema
            BEGIN
                RAISERROR(21263, -1, -1, '@parameters')
                RETURN (1)
            END
        END
        ELSE IF (LTRIM(RTRIM(CONVERT(nvarchar(max), @parameters))) <> N'') -- don't check if the parameters are empty string
        BEGIN
            EXEC dbo.sp_syscollector_validate_xml @collector_type_uid = @collector_type_uid, @parameters = @parameters
        END

        INSERT INTO [dbo].[syscollector_collection_items_internal]
        (
            collection_set_id,
            collector_type_uid,
            name,
            frequency,
            parameters
        )
        VALUES
        (
            @collection_set_id,
            @collector_type_uid,
            @name,
            @frequency,
            @parameters
        )

        SET @collection_item_id = SCOPE_IDENTITY()

        IF (@collection_item_id IS NULL)
        BEGIN
            DECLARE @collection_item_id_as_char VARCHAR(36)
            SELECT @collection_item_id_as_char = CONVERT(VARCHAR(36), @collection_item_id)
            RAISERROR(14262, -1, -1, '@collection_item_id', @collection_item_id_as_char)
            RETURN (1)
        END

        IF (@TranCounter = 0)
            COMMIT TRANSACTION
        RETURN (0)
    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_create_collection_item

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
        
        RETURN (1)
    END CATCH
END
GO

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_update_collection_item_internal]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_update_collection_item_internal]...'
    DROP PROCEDURE [dbo].[sp_syscollector_update_collection_item_internal]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_update_collection_item_internal]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_update_collection_item_internal]
    @collection_item_id        int = NULL,
    @name                    sysname = NULL,
    @new_name                sysname = NULL,
    @frequency                int = NULL,
    @parameters                xml = NULL
AS
BEGIN
    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_update_collection_item
    ELSE
        BEGIN TRANSACTION
    BEGIN TRY
        UPDATE [dbo].[syscollector_collection_items_internal]
        SET
            name                = ISNULL(@new_name, name),
            frequency            = ISNULL(@frequency, frequency),
            parameters            = ISNULL(@parameters, parameters)
        WHERE @collection_item_id = collection_item_id

        IF (@TranCounter = 0)
            COMMIT TRANSACTION
        RETURN (0)
    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_update_collection_item

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
        
        RETURN (1)
    END CATCH
END
GO

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_update_collection_item]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_update_collection_item]...'
    DROP PROCEDURE [dbo].[sp_syscollector_update_collection_item]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_update_collection_item]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_update_collection_item]
    @collection_item_id        int = NULL,
    @name                    sysname = NULL,
    @new_name                sysname = NULL,
    @frequency                int = NULL,
    @parameters                xml = NULL
AS
BEGIN
    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_operator'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_operator')
        RETURN(1) -- Failure
    END

    -- Security checks (restrict functionality for non-dc_admin-s)
    IF ((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1)) 
        AND (@new_name IS NOT NULL))
    BEGIN
        RAISERROR(14676, -1, -1, '@new_name', 'dc_admin')
        RETURN (1) -- Failure
    END
    IF ((NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
        AND (@parameters IS NOT NULL))
    BEGIN
        RAISERROR(14676, -1, -1, '@parameters', 'dc_admin')
        RETURN (1) -- Failure
    END

    DECLARE @retVal int
    EXEC @retVal = dbo.sp_syscollector_verify_collection_item @collection_item_id OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (@retVal)

    IF (@frequency < 5)
    BEGIN
        DECLARE @frequency_as_char VARCHAR(36)
        SELECT @frequency_as_char = CONVERT(VARCHAR(36), @frequency)
        RAISERROR(21405, 16, -1, @frequency_as_char, '@frequency', 5)
        RETURN (1)
    END

    IF (LEN(@new_name) = 0)  -- can't rename to an empty string
    BEGIN
      RAISERROR(21263, -1, -1, '@new_name')
      RETURN(1) -- Failure
    END    

    -- Remove any leading/trailing spaces from parameters
    SET @new_name            = LTRIM(RTRIM(@new_name))

    DECLARE @collection_set_name sysname
    DECLARE @is_system              bit
    DECLARE @is_running             bit
    DECLARE @collector_type_uid     uniqueidentifier
    DECLARE @collection_set_id      int
    SELECT @is_running = s.is_running,
           @is_system = s.is_system,
           @collection_set_name = s.name,
           @collector_type_uid = i.collector_type_uid,
           @collection_set_id = s.collection_set_id
    FROM dbo.syscollector_collection_sets s,
         dbo.syscollector_collection_items i
    WHERE s.collection_set_id = i.collection_set_id
    AND i.collection_item_id = @collection_item_id

    IF (@is_system = 1 AND (@new_name IS NOT NULL OR @parameters IS NOT NULL))
    BEGIN
        -- cannot update, delete, or add new collection items to a system collection set
        RAISERROR(14696, -1, -1);
        RETURN (1)
    END

    IF (@parameters IS NOT NULL)
    BEGIN
        EXEC @retVal = dbo.sp_syscollector_validate_xml @collector_type_uid = @collector_type_uid, @parameters = @parameters
        IF (@retVal <> 0)
            RETURN (@retVal)
    END

    -- if the collection item is running, stop it before update
    IF (@is_running = 1)
    BEGIN
        EXEC @retVal = sp_syscollector_stop_collection_set @collection_set_id = @collection_set_id
        IF (@retVal <> 0)
            RETURN(1)
    END

    -- all conditions go, perform the update
    EXEC @retVal = sp_syscollector_update_collection_item_internal     
                            @collection_item_id = @collection_item_id,
                            @name = @name,
                            @new_name = @new_name,
                            @frequency = @frequency,
                            @parameters = @parameters
                        
    -- if you stopped the collection set, restart it
    IF (@is_running = 1)
    BEGIN
        EXEC @retVal = sp_syscollector_start_collection_set @collection_set_id = @collection_set_id
        IF (@retVal <> 0)
            RETURN (1)
    END
    
    RETURN (0)
END
GO

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_delete_collection_item_internal]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_delete_collection_item_internal]...'
    DROP PROCEDURE [dbo].[sp_syscollector_delete_collection_item_internal]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_delete_collection_item_internal]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_delete_collection_item_internal]
    @collection_item_id         int,
    @name                       sysname
AS
BEGIN
    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_delete_collection_item
    ELSE
        BEGIN TRANSACTION
    BEGIN TRY
        DELETE [dbo].[syscollector_collection_items_internal]
        WHERE collection_item_id = @collection_item_id
          AND name = @name

        IF (@TranCounter = 0)
            COMMIT TRANSACTION
        RETURN (0)
    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_delete_collection_item

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
        
        RETURN (1)
    END CATCH
END
GO

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_delete_collection_item]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_delete_collection_item]...'
    DROP PROCEDURE [dbo].[sp_syscollector_delete_collection_item]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_delete_collection_item]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_delete_collection_item]
    @collection_item_id        int = NULL,
    @name                    sysname = NULL
AS
BEGIN
    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_admin')
        RETURN(1) -- Failure
    END

    DECLARE @retVal int
    EXEC @retVal = dbo.sp_syscollector_verify_collection_item @collection_item_id OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (1)

    DECLARE @is_system          bit
    DECLARE @is_running         bit
    DECLARE @collection_set_id  int
    SELECT @is_running = s.is_running,
           @is_system = s.is_system,
           @collection_set_id = s.collection_set_id
    FROM dbo.syscollector_collection_sets s,
         dbo.syscollector_collection_items i
    WHERE i.collection_item_id = @collection_item_id
    AND s.collection_set_id = i.collection_set_id

    IF (@is_system = 1)
    BEGIN
        -- cannot update, delete, or add new collection items to a system collection set
        RAISERROR(14696, -1, -1);
        RETURN(1)
    END

    IF (@is_running = 1)
    BEGIN
        -- stop the collection set if it was running
        EXEC @retVal = sp_syscollector_stop_collection_set @collection_set_id = @collection_set_id
        IF (@retVal <> 0)
            RETURN (1)
    END

    -- all checks go, perform delete
    EXEC @retVal = sp_syscollector_delete_collection_item_internal @collection_item_id = @collection_item_id, @name = @name
    IF (@retVal <> 0)
        RETURN (1)
        
    RETURN (0)
END
GO

---------------------------------------------------------------
-- Collection Set runtime procedures
---------------------------------------------------------------

IF (NOT OBJECT_ID('dbo.sp_syscollector_start_collection_set', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_start_collection_set]...'
    DROP PROCEDURE [dbo].[sp_syscollector_start_collection_set]
END
GO

PRINT ''
PRINT 'Creating procedure [dbo].[sp_syscollector_start_collection_set]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_start_collection_set]
    @collection_set_id        int = NULL,
    @name                     sysname = NULL
WITH EXECUTE AS OWNER -- 'MS_DataCollectorInternalUser'
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_start_collection_set
    ELSE
        BEGIN TRANSACTION

    BEGIN TRY

    -- Security check (role membership)
    EXECUTE AS CALLER;
    IF (NOT (ISNULL(IS_MEMBER(N'dc_operator'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        REVERT;
        RAISERROR(14677, -1, -1, 'dc_operator')
        RETURN(1) -- Failure
    END
    REVERT;

    -- check if SQL Server Agent is enabled
    DECLARE @agent_enabled int
    SELECT @agent_enabled = CAST(value_in_use AS int) FROM sys.configurations WHERE name = N'Agent XPs'
    IF @agent_enabled <> 1
    BEGIN
        RAISERROR(14688, -1, -1)
        RETURN (1)
    END

    -- check if MDW is setup
    DECLARE @instance_name sysname
    SELECT @instance_name = CONVERT(sysname,parameter_value)
    FROM [msdb].[dbo].[syscollector_config_store_internal]
    WHERE parameter_name = N'MDWInstance'
    IF (@instance_name IS NULL)
    BEGIN
        RAISERROR(14689, -1, -1)
        RETURN (1)
    END    
    DECLARE @database_name sysname
    SELECT @database_name = CONVERT(sysname,parameter_value)
    FROM [msdb].[dbo].[syscollector_config_store_internal]
    WHERE parameter_name = N'MDWDatabase'
    IF (@database_name IS NULL)
    BEGIN
        RAISERROR(14689, -1, -1)
        RETURN (1)
    END

    -- Verify the input parameters
    DECLARE @retVal int
    EXEC @retVal = dbo.sp_syscollector_verify_collection_set @collection_set_id OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (1)

    -- Check if the collection set does not have any collection items
    IF NOT EXISTS(
        SELECT i.collection_item_id 
        FROM [dbo].[syscollector_collection_sets] AS s
        INNER JOIN [dbo].[syscollector_collection_items] AS i
            ON(s.collection_set_id = i.collection_set_id)
        WHERE s.collection_set_id = @collection_set_id
    )
    BEGIN
        RAISERROR(14685, 10, -1, @name) -- Raise a warning message
        IF (@TranCounter = 0)
            COMMIT TRANSACTION
        RETURN (0)
    END

    DECLARE @proxy_id int;
    DECLARE @collection_job_id uniqueidentifier
    DECLARE @upload_job_id uniqueidentifier
    DECLARE @schedule_uid uniqueidentifier;

    SELECT 
        @collection_job_id = collection_job_id, 
        @upload_job_id = upload_job_id, 
        @proxy_id = proxy_id,
        @schedule_uid = schedule_uid
    FROM [dbo].[syscollector_collection_sets_internal]
    WHERE collection_set_id = @collection_set_id;

    -- Check if the set does not have a proxy
    IF (@proxy_id IS NULL)
    BEGIN
        -- to start a collection set without a proxy, the caller has to be a sysadmin
        EXECUTE AS CALLER;
            IF (NOT (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1))
            BEGIN
                REVERT;
                RAISERROR(14692, -1, -1, @name)
                RETURN (1)
            END
        REVERT;
    END

    -- Starting a collection set requires a schedule
    IF @schedule_uid IS NULL
    BEGIN
        RAISERROR(14693, -1, -1)
        RETURN (1)
    END

    -- Check if we have jobs created, and if not, create them
    IF (@collection_job_id IS NULL AND @upload_job_id IS NULL)
    BEGIN
        -- Jobs not created yet, go and crete them
        -- We need to get some data from collection_sets table 
        -- before we do that.
        DECLARE @collection_set_uid uniqueidentifier;
        DECLARE @schedule_id int;
        DECLARE @collection_mode int;

        SELECT 
            @collection_set_uid = collection_set_uid,
            @collection_mode = collection_mode
        FROM
            [dbo].[syscollector_collection_sets_internal]
        WHERE
            collection_set_id = @collection_set_id;
        
        -- Sanity check
        -- Make sure the proxy and schedule are still there, someone could have
        -- remove them between when the collection set was created and now.
        IF (@proxy_id IS NOT NULL)
        BEGIN
            DECLARE @proxy_name sysname
            
            -- this will throw if the id does not exist
            EXEC @retVal = sp_verify_proxy_identifiers '@proxy_name', '@proxy_id', @proxy_name OUTPUT, @proxy_id OUTPUT
            IF (@retVal <> 0)
                RETURN (1)
        END

        SELECT @schedule_id = schedule_id FROM sysschedules_localserver_view WHERE @schedule_uid = schedule_uid
        EXEC @retVal = sp_verify_schedule_identifiers  @name_of_name_parameter = '@schedule_name',
                                                       @name_of_id_parameter   = '@schedule_id',
                                                       @schedule_name          = NULL,
                                                       @schedule_id            = @schedule_id,
                                                       @owner_sid              = NULL,
                                                       @orig_server_id         = NULL 
        IF (@retVal <> 0)
            RETURN (1)

        -- Go add the jobs
        EXEC [dbo].[sp_syscollector_create_jobs]
            @collection_set_id    = @collection_set_id,
            @collection_set_uid = @collection_set_uid,
            @collection_set_name = @name,
            @proxy_id            = @proxy_id,
            @schedule_id        = @schedule_id,
            @collection_mode    = @collection_mode,
            @collection_job_id    = @collection_job_id OUTPUT,
            @upload_job_id        = @upload_job_id OUTPUT

        -- Finally, update the collection_sets table
        UPDATE [dbo].[syscollector_collection_sets_internal]
        SET
            upload_job_id        = @upload_job_id,
            collection_job_id    = @collection_job_id
        WHERE @collection_set_id = collection_set_id
    END

    -- Update the is_running column for this collection set
    -- There is a trigger defined for that table that turns on
    -- the collection and upload jobs in response to that bit
    -- changing.
    UPDATE [dbo].[syscollector_collection_sets_internal]
    SET is_running = 1
    WHERE collection_set_id = @collection_set_id

    IF (@TranCounter = 0)
        COMMIT TRANSACTION
    RETURN (0)

    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_start_collection_set

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);

        RETURN (1)        
    END CATCH
END
GO


IF (NOT OBJECT_ID('dbo.sp_syscollector_stop_collection_set', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_stop_collection_set]...'
    DROP PROCEDURE [dbo].[sp_syscollector_stop_collection_set]
END
GO

PRINT ''
PRINT 'Creating procedure [dbo].[sp_syscollector_stop_collection_set]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_stop_collection_set]
    @collection_set_id        int = NULL,
    @name                     sysname = NULL,
    @stop_collection_job      bit = 1           -- Do we need to stop the collection job, YES by default
AS
BEGIN
    SET NOCOUNT ON

    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_operator'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_operator')
        RETURN(1) -- Failure
    END

    -- Verify the input parameters
    DECLARE @retVal int
    EXEC @retVal = dbo.sp_syscollector_verify_collection_set @collection_set_id OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (1)

    IF (@stop_collection_job = 1)
    BEGIN
        DECLARE @collection_mode INT
        DECLARE @collection_job_id UNIQUEIDENTIFIER

        SELECT  @collection_mode = collection_mode, @collection_job_id = collection_job_id
        FROM    dbo.syscollector_collection_sets
        WHERE   collection_set_id = @collection_set_id
       
        DECLARE @is_collection_job_running INT
        EXECUTE [dbo].[sp_syscollector_get_collection_set_execution_status]
                @collection_set_id = @collection_set_id,
                @is_collection_running = @is_collection_job_running OUTPUT

        -- Stop the collection job if we are in cached mode, this should signal the runtime to exit
        IF (@is_collection_job_running = 1      -- Collection job is running
            AND @collection_mode = 0
            AND @stop_collection_job = 1)
        BEGIN
            EXEC sp_stop_job @job_id = @collection_job_id
        END
    END


    -- Update the is_running column for this collection set
    -- There is a trigger defined for that table that turns off
    -- the collection and uplaod jobs in response to that bit
    -- changing.
    UPDATE [dbo].[syscollector_collection_sets_internal]
    SET is_running = 0
    WHERE collection_set_id = @collection_set_id

    RETURN (0)
END
GO


IF (NOT OBJECT_ID('[dbo].[sp_syscollector_get_collection_set_execution_status]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_get_collection_set_execution_status]...'
    DROP PROCEDURE [dbo].[sp_syscollector_get_collection_set_execution_status]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_get_collection_set_execution_status]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_get_collection_set_execution_status]
    @collection_set_id            int,
    @is_running                    int = NULL OUTPUT,
    @is_collection_running        int = NULL OUTPUT,
    @collection_job_state        int = NULL OUTPUT,
    @is_upload_running            int = NULL OUTPUT,
    @upload_job_state            int = NULL OUTPUT
WITH EXECUTE AS OWNER -- 'MS_DataCollectorInternalUser'
AS
BEGIN
    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_get_execution_status
    ELSE
        BEGIN TRANSACTION

    BEGIN TRY

    DECLARE @xp_results TABLE (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)


    DECLARE @is_sysadmin INT
    SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)

    DECLARE @collection_job_id UNIQUEIDENTIFIER
    DECLARE @upload_job_id UNIQUEIDENTIFIER
    
    SELECT @collection_job_id = collection_job_id, @upload_job_id = upload_job_id 
    FROM dbo.syscollector_collection_sets WHERE collection_set_id = @collection_set_id

    DECLARE @agent_enabled int
    SELECT @agent_enabled = CAST(value_in_use AS int) FROM sys.configurations WHERE name = N'Agent XPs'

    IF (@agent_enabled <> 0)
    BEGIN
        INSERT  INTO @xp_results    
        EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, N'', @upload_job_id

        INSERT  INTO @xp_results    
        EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, N'', @collection_job_id
    END

    SELECT @is_running = is_running FROM dbo.syscollector_collection_sets WHERE collection_set_id = @collection_set_id
    SELECT @is_collection_running = running, @collection_job_state = job_state FROM @xp_results WHERE job_id = @collection_job_id
    SELECT @is_upload_running = running, @upload_job_state = job_state FROM @xp_results WHERE job_id = @upload_job_id

    IF (@TranCounter = 0)
        COMMIT TRANSACTION
    RETURN (0)

    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_get_execution_status

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);

        RETURN (1)        
    END CATCH
END
GO


IF (NOT OBJECT_ID('dbo.sp_syscollector_upload_collection_set', 'P') IS NULL)
BEGIN
    DROP PROCEDURE [dbo].[sp_syscollector_upload_collection_set]
END
GO

PRINT ''
PRINT 'Creating procedure [dbo].[sp_syscollector_upload_collection_set]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_upload_collection_set]
    @collection_set_id        int = NULL,
    @name                     sysname = NULL
WITH EXECUTE AS OWNER -- 'MS_DataCollectorInternalUser'
AS
BEGIN
    SET NOCOUNT ON

    -- Security check (role membership)
    EXECUTE AS CALLER;
    IF (NOT (ISNULL(IS_MEMBER(N'dc_operator'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        REVERT;
        RAISERROR(14677, -1, -1, 'dc_operator')
        RETURN(1) -- Failure
    END
    REVERT;

    -- Verify the input parameters
    DECLARE @retVal int
    EXEC @retVal = dbo.sp_syscollector_verify_collection_set @collection_set_id OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (1)

    -- Make sure the collection set is running and is in the right mode
    DECLARE @is_running bit
    DECLARE @collection_mode smallint

    SELECT 
        @is_running = is_running,
        @collection_mode = collection_mode            
    FROM [dbo].[syscollector_collection_sets]
    WHERE collection_set_id = @collection_set_id

    IF (@collection_mode <> 0)
    BEGIN
        RAISERROR(14694, -1, -1, @name)
        RETURN(1)
    END

    IF (@is_running = 0)
    BEGIN
        RAISERROR(14674, -1, -1, @name)
        RETURN(1)
    END

    -- Make sure the collector is enabled
    EXEC @retVal = [dbo].[sp_syscollector_verify_collector_state] @desired_state = 1
    IF (@retVal <> 0)
        RETURN (1)

    -- Check if the upload job is currently running
    DECLARE @is_upload_job_running INT
    EXECUTE [dbo].[sp_syscollector_get_collection_set_execution_status]
        @collection_set_id = @collection_set_id,
        @is_upload_running = @is_upload_job_running OUTPUT

    IF (@is_upload_job_running = 0)
    BEGIN
        -- Job is not running, we can trigger it now
        DECLARE @job_id UNIQUEIDENTIFIER
        SELECT @job_id = upload_job_id 
            FROM [dbo].[syscollector_collection_sets] 
            WHERE collection_set_id = @collection_set_id

        EXEC @retVal = sp_start_job @job_id = @job_id
        IF (@retVal <> 0)
            RETURN (1)
    END

    RETURN (0)
END
GO


IF (NOT OBJECT_ID('dbo.sp_syscollector_run_collection_set', 'P') IS NULL)
BEGIN
    DROP PROCEDURE [dbo].[sp_syscollector_run_collection_set]
END
GO

PRINT ''
PRINT 'Creating procedure [dbo].[sp_syscollector_run_collection_set]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_run_collection_set]
    @collection_set_id        int = NULL,
    @name                     sysname = NULL
WITH EXECUTE AS OWNER -- 'MS_DataCollectorInternalUser'
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_run_collection_set
    ELSE
        BEGIN TRANSACTION

    BEGIN TRY


    -- Security check (role membership)
    EXECUTE AS CALLER;
    IF (NOT (ISNULL(IS_MEMBER(N'dc_operator'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        REVERT;
        RAISERROR(14677, -1, -1, 'dc_operator')
        RETURN(1) -- Failure
    END
    REVERT;

    -- Verify the input parameters
    DECLARE @retVal int
    EXEC @retVal = dbo.sp_syscollector_verify_collection_set @collection_set_id OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (1)

    -- Make sure the collection set is in the right mode
    DECLARE @collection_mode smallint
    DECLARE @collection_set_uid uniqueidentifier;

    SELECT 
        @collection_set_uid = collection_set_uid,
        @collection_mode = collection_mode            
    FROM [dbo].[syscollector_collection_sets]
    WHERE collection_set_id = @collection_set_id

    IF (@collection_mode <> 1)
    BEGIN
        RAISERROR(14695, -1, -1, @name)
        RETURN(1)
    END


    -- Make sure the collector is enabled
    EXEC @retVal = [dbo].[sp_syscollector_verify_collector_state] @desired_state = 1
    IF (@retVal <> 0)
        RETURN (1)

    -- check if SQL Server Agent is enabled
    DECLARE @agent_enabled int
    SELECT @agent_enabled = CAST(value_in_use AS int) FROM sys.configurations WHERE name = N'Agent XPs'
    IF @agent_enabled <> 1
    BEGIN
        RAISERROR(14688, -1, -1)
        RETURN (1)
    END

    -- check if MDW is setup
    DECLARE @instance_name sysname
    SELECT @instance_name = CONVERT(sysname,parameter_value)
    FROM [msdb].[dbo].[syscollector_config_store_internal]
    WHERE parameter_name = N'MDWInstance'
    IF (@instance_name IS NULL)
    BEGIN
        RAISERROR(14689, -1, -1)
        RETURN (1)
    END    
    DECLARE @database_name sysname
    SELECT @database_name = CONVERT(sysname,parameter_value)
    FROM [msdb].[dbo].[syscollector_config_store_internal]
    WHERE parameter_name = N'MDWDatabase'
    IF (@database_name IS NULL)
    BEGIN
        RAISERROR(14689, -1, -1)
        RETURN (1)
    END

    -- Make sure the jobs are created for the collection set
    -- Verify the input parameters
    EXEC @retVal = dbo.sp_syscollector_verify_collection_set @collection_set_id OUTPUT, @name OUTPUT
    IF (@retVal <> 0)
        RETURN (1)

    -- Check if the collection set does not have any collection items
    IF NOT EXISTS(
        SELECT i.collection_item_id 
        FROM [dbo].[syscollector_collection_sets] AS s
        INNER JOIN [dbo].[syscollector_collection_items] AS i
            ON(s.collection_set_id = i.collection_set_id)
        WHERE s.collection_set_id = @collection_set_id
    )
    BEGIN
        RAISERROR(14685, 10, -1, @name) -- Raise a warning message
        IF (@TranCounter = 0)
            COMMIT TRANSACTION
        RETURN (0)
    END

    DECLARE @proxy_id int;
    DECLARE @collection_job_id uniqueidentifier
    DECLARE @upload_job_id uniqueidentifier

    SELECT @collection_job_id = collection_job_id, 
           @upload_job_id = upload_job_id, 
           @proxy_id = proxy_id
    FROM [dbo].[syscollector_collection_sets_internal]
    WHERE collection_set_id = @collection_set_id;

    -- Check if the set does not have a proxy
    IF (@proxy_id IS NULL)
    BEGIN
        -- to start a collection set without a proxy, the caller has to be a sysadmin
        EXECUTE AS CALLER;
            IF (NOT (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1))
            BEGIN
                REVERT;
                RAISERROR(14692, -1, -1, @name)
                RETURN (1)
            END
        REVERT;
    END

    -- Check if we have jobs created, and if not, create them
    DECLARE @jobs_just_created bit
    SET @jobs_just_created = 0  -- False until further notice
    IF (@collection_job_id IS NULL AND @upload_job_id IS NULL)
    BEGIN
        DECLARE @schedule_id int;
        DECLARE @schedule_uid uniqueidentifier;

        SELECT 
            @schedule_uid = schedule_uid
        FROM [dbo].[syscollector_collection_sets_internal]
        WHERE collection_set_id = @collection_set_id;
        
        IF (@schedule_uid IS NOT NULL)
        BEGIN
            SELECT @schedule_id = schedule_id FROM sysschedules_localserver_view WHERE @schedule_uid = schedule_uid
        END

        -- Sanity check
        -- Make sure the proxy and schedule are still there, someone could have
        -- remove them between when the collection set was created and now.
        IF (@proxy_id IS NOT NULL)
        BEGIN
            DECLARE @proxy_name sysname
            
            -- this will throw an error of proxy_id does not exist
            EXEC @retVal = msdb.dbo.sp_verify_proxy_identifiers '@proxy_name', '@proxy_id', @proxy_name OUTPUT, @proxy_id OUTPUT
            IF (@retVal <> 0)
                RETURN (0)
        END

        IF (@schedule_uid IS NOT NULL)
        BEGIN
            EXEC @retVal = sp_verify_schedule_identifiers  @name_of_name_parameter = '@schedule_name',
                                                           @name_of_id_parameter   = '@schedule_id',
                                                           @schedule_name          = NULL,
                                                           @schedule_id            = @schedule_id,
                                                           @owner_sid              = NULL,
                                                           @orig_server_id         = NULL 
            IF (@retVal <> 0)
                RETURN (1)
        END

        -- Go add the jobs
        EXEC [dbo].[sp_syscollector_create_jobs]
            @collection_set_id    = @collection_set_id,
            @collection_set_uid = @collection_set_uid,
            @collection_set_name = @name,
            @proxy_id            = @proxy_id,
            @schedule_id        = @schedule_id,
            @collection_mode    = @collection_mode,
            @collection_job_id    = @collection_job_id OUTPUT,
            @upload_job_id        = @upload_job_id OUTPUT

        -- Finally, update the collection_sets table
        UPDATE [dbo].[syscollector_collection_sets_internal]
        SET
            upload_job_id        = @upload_job_id,
            collection_job_id    = @collection_job_id
        WHERE @collection_set_id = collection_set_id

        SET @jobs_just_created = 1  -- Record the fact that we have just created the job here
    END

    IF (@jobs_just_created = 1)
    BEGIN  -- We created the jobs here in this transaction, post a request for agent to start as soon as we commit
        EXEC @retVal = sp_start_job @job_id = @upload_job_id
        IF (@retVal <> 0)
            RETURN (1)
    END
    ELSE   
    BEGIN
        -- The jobs were created previously, we need to guard against it already executing by the schedule
        -- So, check if the job is currently running before asking agent to start it
        DECLARE @is_upload_job_running INT
        EXECUTE [dbo].[sp_syscollector_get_collection_set_execution_status]
            @collection_set_id = @collection_set_id,
            @is_upload_running = @is_upload_job_running OUTPUT

        IF (@is_upload_job_running = 0)
        BEGIN
            -- Job is not running, we can trigger it now
            -- We run only one job because for this (non-cached) mode there is only one job. The same id is stored
            -- as collection and upload job id
            EXEC @retVal = sp_start_job @job_id = @upload_job_id
            IF (@retVal <> 0)
                RETURN (1)
        END
    END

    IF (@TranCounter = 0)
        COMMIT TRANSACTION
    RETURN (0)

    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_run_collection_set

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);

        RETURN (1)        
    END CATCH
END
GO


PRINT 'Creating trigger [dbo].[syscollector_collection_set_is_running_update_trigger] on [dbo].[syscollector_collection_sets_internal]'
GO
IF NOT OBJECT_ID('dbo.syscollector_collection_set_is_running_update_trigger', 'TR') IS NULL
    DROP TRIGGER [dbo].[syscollector_collection_set_is_running_update_trigger]
GO

CREATE TRIGGER [dbo].[syscollector_collection_set_is_running_update_trigger] on [dbo].[syscollector_collection_sets_internal]
WITH EXECUTE AS OWNER -- 'MS_DataCollectorInternalUser'
FOR UPDATE
AS
BEGIN
    DECLARE @collection_set_id INT
    DECLARE @is_running BIT
    DECLARE @old_is_running BIT
    DECLARE @collection_mode SMALLINT

    IF (NOT UPDATE (is_running))
       RETURN

    DECLARE @collector_enabled int
    SET @collector_enabled = CONVERT(int, (SELECT parameter_value FROM dbo.syscollector_config_store_internal
                            WHERE parameter_name = 'CollectorEnabled'))
    IF @collector_enabled = 0
    BEGIN
        -- flipping the is_running bit has no effect when the collector is disabled
        RAISERROR(14682, 10, -1) -- severity 10 emits a warning
    END
    ELSE
    BEGIN
        DECLARE inserted_cursor CURSOR LOCAL FOR 
            SELECT collection_set_id, is_running, collection_mode
            FROM inserted 

        OPEN inserted_cursor
        FETCH inserted_cursor INTO @collection_set_id, @is_running, @collection_mode
        
        WHILE @@FETCH_STATUS = 0 
        BEGIN
            SELECT @old_is_running = is_running FROM deleted WHERE collection_set_id = @collection_set_id

            -- If there is a change in the state, handle accordingly
            IF (@old_is_running <> @is_running)
            BEGIN
                IF (@is_running = 0)
                BEGIN
                    EXEC dbo.sp_syscollector_stop_collection_set_jobs @collection_set_id = @collection_set_id
                END
                ELSE IF (@is_running = 1)
                BEGIN              
                    EXEC dbo.sp_syscollector_start_collection_set_jobs @collection_set_id = @collection_set_id
                END
            END

            FETCH inserted_cursor INTO @collection_set_id, @is_running, @collection_mode
        END    

        CLOSE inserted_cursor
        DEALLOCATE inserted_cursor
    END
END
GO

PRINT ''
PRINT 'Creating stored procedure syscollector_stop_collection_set_jobs...'

IF (NOT OBJECT_ID('dbo.sp_syscollector_stop_collection_set_jobs', 'P') IS NULL)
    DROP PROCEDURE [dbo].[sp_syscollector_stop_collection_set_jobs]
GO

CREATE PROCEDURE [dbo].[sp_syscollector_stop_collection_set_jobs]
    @collection_set_id    int
AS
BEGIN
    SET NOCOUNT ON

    -- Collection set stopped. Make sure the following happens:
    -- 1. Detach upload schedule
    -- 2. Collection job is stopped
    -- 3. Upload job is kicked once if it is not running now
    -- 4. Collection and upload jobs are disabled
    -- 5. Attach upload schedule
    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_stop_collection_set_jobs
    ELSE
        BEGIN TRANSACTION
    
    BEGIN TRY
        DECLARE @collection_job_id    uniqueidentifier
        DECLARE @upload_job_id        uniqueidentifier
        DECLARE @schedule_uid        uniqueidentifier
        DECLARE @collection_mode    smallint

        SELECT    @collection_job_id = collection_job_id, 
                @upload_job_id = upload_job_id, 
                @collection_mode = collection_mode, 
                @schedule_uid = schedule_uid
        FROM dbo.syscollector_collection_sets
        WHERE collection_set_id = @collection_set_id

        DECLARE @schedule_id int
        IF (@collection_mode != 1)  -- detach schedule for continuous and snapshot modes
        BEGIN
            SELECT @schedule_id = schedule_id from sysschedules_localserver_view WHERE @schedule_uid = schedule_uid
            IF (@schedule_id IS NULL)
            BEGIN
                DECLARE @schedule_uid_as_char VARCHAR(36)
                SELECT @schedule_uid_as_char = CONVERT(VARCHAR(36), @schedule_uid)
                RAISERROR(14262, -1, -1, '@schedule_uid', @schedule_uid_as_char)
                RETURN (1)
            END

            -- Detach schedule
            EXEC dbo.sp_detach_schedule
                @job_id            = @upload_job_id,
                @schedule_id    = @schedule_id,
                @delete_unused_schedule = 0    -- do not delete schedule, might need to attach it back again
        END

        DECLARE @is_upload_job_running INT
        EXECUTE [dbo].[sp_syscollector_get_collection_set_execution_status]
                @collection_set_id = @collection_set_id,
                @is_upload_running = @is_upload_job_running OUTPUT

        -- Upload job (needs to be kicked off for continuous collection mode)
        IF (@is_upload_job_running = 0            -- If the upload job is not already in progress
            AND @collection_mode = 0)           -- don't do it for adhoc or snapshot, they will handle it on their own
        BEGIN
            EXEC sp_start_job @job_id = @upload_job_id, @error_flag = 0
        END

        -- Disable both jobs
        EXEC sp_update_job @job_id = @collection_job_id, @enabled = 0
        EXEC sp_update_job @job_id = @upload_job_id, @enabled = 0

        IF (@collection_mode != 1)    -- attach schedule for continuous and snapshot modes
        BEGIN
            -- Attach schedule
            EXEC dbo.sp_attach_schedule
                @job_id            = @upload_job_id,
                @schedule_id    = @schedule_id
        END

        -- Log the stop of the collection set
        EXEC sp_syscollector_event_oncollectionstop @collection_set_id = @collection_set_id

        IF (@TranCounter = 0)
            COMMIT TRANSACTION
        RETURN (0)
    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_stop_collection_set_jobs

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
        
        RETURN (1)
    END CATCH
END
GO


PRINT ''
PRINT 'Creating stored procedure syscollector_start_collection_set_jobs...'

IF (NOT OBJECT_ID('dbo.sp_syscollector_start_collection_set_jobs', 'P') IS NULL)
    DROP PROCEDURE [dbo].[sp_syscollector_start_collection_set_jobs]
GO

CREATE PROCEDURE [dbo].[sp_syscollector_start_collection_set_jobs]
    @collection_set_id    int
AS
BEGIN
    SET NOCOUNT ON

    -- Collection set started. Make sure the following happens:
    -- 1. Collection and upload jobs are enabled
    -- 2. Collection job is started if it is defined as running continously

    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_start_collection_set_jobs
    ELSE
        BEGIN TRANSACTION

    BEGIN TRY
        
        -- Log the start of the collection set
        DECLARE @log_id bigint
        EXEC sp_syscollector_event_oncollectionstart @collection_set_id = @collection_set_id, @log_id = @log_id OUTPUT

        -- Enable both jobs
        DECLARE @collection_job_id    uniqueidentifier
        DECLARE @upload_job_id        uniqueidentifier
        DECLARE @collection_mode    smallint

        SELECT    @collection_job_id = collection_job_id,
                @upload_job_id = upload_job_id,
                @collection_mode = collection_mode
        FROM dbo.syscollector_collection_sets
        WHERE collection_set_id = @collection_set_id

        EXEC sp_update_job @job_id = @collection_job_id, @enabled = 1
        EXEC sp_update_job @job_id = @upload_job_id, @enabled = 1

        -- Start the collection job if you are in ad hoc or continuous modes
        IF (@collection_mode = 1 OR @collection_mode = 0)
        BEGIN
            EXEC sp_start_job @job_id = @collection_job_id, @error_flag = 0
        END

        IF (@TranCounter = 0)
            COMMIT TRANSACTION
        RETURN (0)
    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_start_collection_set_jobs

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
        
        RETURN (1)
    END CATCH
END
GO


---------------------------------------------------------------
-- Collection Set execution log                                        
---------------------------------------------------------------

IF (OBJECT_ID('dbo.syscollector_execution_log_internal', 'U') IS NULL)
BEGIN
    PRINT ''
    PRINT 'Creating table syscollector_execution_log_internal...'

    CREATE TABLE [dbo].[syscollector_execution_log_internal]
    (
        log_id                  bigint          IDENTITY(1,1) NOT NULL,     -- Unique log entry id. Each execution log gets a new one
        parent_log_id           bigint          NULL,                       -- Id of the parent execution context. NULL for the root node. 
        collection_set_id       int             NOT NULL,                   -- Id of the collection set that owns this entry
        collection_item_id      int             NULL,                       -- Collection item id
        start_time              datetime        NOT NULL,                   -- Collection set or package execution start time
        last_iteration_time     datetime        NULL,                       -- For continously running packages, last time an interation has been started
        finish_time             datetime        NULL,                       -- Collection set or package execution end time
        runtime_execution_mode  smallint        NULL,                       -- 0 - Collection, 1 - Upload
        status                  smallint        NOT NULL,                   -- 0 - Running, 1 - Finished, 2 - Error, 3 - Warning
        operator                nvarchar(128)   NOT NULL,                   -- Name of the user running the collection set or package
        package_id              uniqueidentifier NULL,                      -- Id of a package, NULL for collection sets
        package_execution_id    uniqueidentifier NULL,                      -- Execution Id generated by SSIS for each package execution. Use to join events from sysssislog. NULL for collection sets
        failure_message         nvarchar(2048)   NULL                       -- Message that indicates package failure. NULL if no failure

        CONSTRAINT [PK_syscollector_execution_log] PRIMARY KEY CLUSTERED (log_id ASC),
        CONSTRAINT [FK_syscollector_execution_log_collection_set_id] FOREIGN KEY (collection_set_id) 
            REFERENCES [dbo].[syscollector_collection_sets_internal] (collection_set_id),
    )
END
go

PRINT ''
PRINT 'Creating view syscollector_execution_log...'

IF (NOT OBJECT_ID('dbo.syscollector_execution_log', 'V') IS NULL)
    DROP VIEW [dbo].[syscollector_execution_log]
go

CREATE VIEW [dbo].[syscollector_execution_log] AS
    SELECT 
        log_id, 
        ISNULL(parent_log_id, 0) as parent_log_id, 
        collection_set_id, 
        collection_item_id,
        start_time,
        last_iteration_time,
        finish_time,
        runtime_execution_mode,
        [status],
        operator,
        package_id,
        msdb.dbo.fn_syscollector_get_package_path(package_id) as package_name,
        package_execution_id,
        failure_message
    FROM dbo.syscollector_execution_log_internal;
GO

IF (NOT OBJECT_ID('[dbo].[sp_syscollector_delete_jobs]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_delete_jobs]...'
    DROP PROCEDURE [dbo].[sp_syscollector_delete_jobs]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_delete_jobs]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_delete_jobs]
    @collection_job_id        uniqueidentifier,
    @upload_job_id            uniqueidentifier,
    @schedule_id            int = NULL,
    @collection_mode        smallint
AS
BEGIN
    -- delete the jobs corresponding to the collection set
    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_syscollector_delete_jobs
    ELSE
        BEGIN TRANSACTION
    
    BEGIN TRY

    IF (@collection_mode = 1) -- non-cached mode
    BEGIN
        IF (@upload_job_id IS NOT NULL)
        BEGIN
            -- note, upload job id = collection job id in this mode
            IF (@schedule_id IS NOT NULL)
            BEGIN
                EXEC dbo.sp_detach_schedule
                    @job_id            = @upload_job_id, 
                    @schedule_id    = @schedule_id,
                    @delete_unused_schedule = 0
            END

            EXEC dbo.sp_delete_jobserver
                @job_id            = @upload_job_id,
                @server_name    = N'(local)'

            EXEC dbo.sp_delete_job 
                @job_id            = @upload_job_id
        END
    END
    ELSE -- cached mode
    BEGIN
        -- detach schedules, delete job servers, then delete jobs
        IF (@upload_job_id IS NOT NULL)
        BEGIN
            EXEC dbo.sp_detach_schedule
                @job_id            = @upload_job_id, 
                @schedule_id    = @schedule_id,
                @delete_unused_schedule = 0

            EXEC dbo.sp_delete_jobserver
                @job_id            = @upload_job_id,
                @server_name    = N'(local)'

            EXEC dbo.sp_delete_job 
                @job_id            = @upload_job_id
        END

        IF (@collection_job_id IS NOT NULL)
        BEGIN
            EXEC dbo.sp_detach_schedule
                @job_id            = @collection_job_id, 
                @schedule_name    = N'RunAsSQLAgentServiceStartSchedule',
                @delete_unused_schedule = 0

            EXEC dbo.sp_delete_jobserver
                @job_id            = @collection_job_id,
                @server_name    = N'(local)'

            EXEC dbo.sp_delete_job 
                @job_id            = @collection_job_id
        END
    END

    IF (@TranCounter = 0)
        COMMIT TRANSACTION
    RETURN (0)
    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_syscollector_delete_jobs

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
        
        RETURN (1)
    END CATCH
END
GO


---------------------------------------------------------------
-- Collection Set execution stats                                      
---------------------------------------------------------------

IF (OBJECT_ID('dbo.syscollector_execution_stats_internal', 'U') IS NULL)
BEGIN
    PRINT ''
    PRINT 'Creating table syscollector_execution_stats_internal...'

    CREATE TABLE [dbo].[syscollector_execution_stats_internal]
    (
        log_id                  bigint          NOT NULL,                   -- Log_id of the package that inserts the row
        task_name               nvarchar(128)   NOT NULL,                   -- Name of the task/component in the package that reports the execution stats
        execution_row_count_in  int             NULL,                       -- Number of rows that entered the data flow from its source transformations
        execution_row_count_out int             NULL,                       -- Number of rows that exited the data flow into its destination transformations
        execution_row_count_errors int          NULL,                       -- Number of rows that were re-directed to an error output due to processing errors
        execution_time_ms       int             NULL,                       -- Execution time of the data flow
        log_time                datetime        NOT NULL                    -- Date and time when this entry was logged

        CONSTRAINT [PK_syscollector_execution_stats] PRIMARY KEY CLUSTERED (log_id ASC, task_name ASC, log_time DESC),
        CONSTRAINT [FK_syscollector_execution_stats_log_id] FOREIGN KEY (log_id) REFERENCES [dbo].[syscollector_execution_log_internal] (log_id) ON DELETE CASCADE
    )
END
go

PRINT ''
PRINT 'Creating view syscollector_execution_stats...'

IF (NOT OBJECT_ID('dbo.syscollector_execution_stats', 'V') IS NULL)
    DROP VIEW [dbo].[syscollector_execution_stats]
go

CREATE VIEW [dbo].[syscollector_execution_stats] AS
    SELECT
        log_id,
        task_name,
        execution_row_count_in,
        execution_row_count_out,
        execution_row_count_errors,
        execution_time_ms,
        log_time
    FROM dbo.syscollector_execution_stats_internal
go

---------------------------------------------------------------
-- Logging stored procedures  
---------------------------------------------------------------

PRINT ''
PRINT 'Creating stored procedure sp_syscollector_verify_event_log_id...'

IF (NOT OBJECT_ID('dbo.sp_syscollector_verify_event_log_id', 'P') IS NULL)
    DROP PROCEDURE [dbo].[sp_syscollector_verify_event_log_id]
go

CREATE PROCEDURE [dbo].[sp_syscollector_verify_event_log_id]
    @log_id bigint,
    @allow_collection_set_id bit = 0
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @log_id_as_char VARCHAR(36)

    IF (@log_id IS NULL)
    BEGIN
        RAISERROR(14606, -1, -1, '@log_id')
        RETURN (1)
    END
    ELSE IF @allow_collection_set_id = 0
    BEGIN
        IF (NOT EXISTS (SELECT log_id FROM dbo.syscollector_execution_log WHERE log_id = @log_id AND package_id IS NOT NULL))
        BEGIN
            SELECT @log_id_as_char = CONVERT(VARCHAR(36), @log_id)

            RAISERROR(14262, -1, -1, '@log_id', @log_id_as_char)
            RETURN (1)
        END
    END
    ELSE
    BEGIN
        IF (NOT EXISTS (SELECT log_id FROM dbo.syscollector_execution_log WHERE log_id = @log_id))
        BEGIN
            SELECT @log_id_as_char = CONVERT(VARCHAR(36), @log_id)

            RAISERROR(14262, -1, -1, '@log_id', @log_id_as_char)
            RETURN (1)
        END
    END

    RETURN (0)
END
go

PRINT ''
PRINT 'Creating stored procedure sp_syscollector_event_oncollectionstart...'

IF (NOT OBJECT_ID('dbo.sp_syscollector_event_oncollectionstart', 'P') IS NULL)
    DROP PROCEDURE [dbo].[sp_syscollector_event_oncollectionstart]
go

CREATE PROCEDURE [dbo].[sp_syscollector_event_oncollectionstart]
    @collection_set_id int,
    @operator nvarchar(128) = NULL,
    @log_id bigint OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_operator'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_operator')
        RETURN(1) -- Failure
    END

    -- Verify parameters
    --

    -- Check the collection_set_id
    IF (@collection_set_id IS NULL)
    BEGIN
        RAISERROR(14606, -1, -1, '@collection_set_id')
        RETURN (1)
    END
    ELSE IF (NOT EXISTS (SELECT collection_set_id FROM dbo.syscollector_collection_sets WHERE collection_set_id = @collection_set_id))
    BEGIN
        DECLARE @collection_set_id_as_char VARCHAR(36)
        SELECT @collection_set_id_as_char = CONVERT(VARCHAR(36), @collection_set_id)

        RAISERROR(14262, -1, -1, '@collection_set_id', @collection_set_id_as_char)
        RETURN (1)
    END


    -- Default operator to currently logged in user
    SET @operator = NULLIF(LTRIM(RTRIM(@operator)), '')
    SET @operator = ISNULL(@operator, suser_sname())

    -- Insert the log record
    --
    INSERT INTO dbo.syscollector_execution_log_internal (
        parent_log_id, 
        collection_set_id, 
        start_time,
        last_iteration_time,
        finish_time,
        runtime_execution_mode,
        [status],
        operator,
        package_id,
        package_execution_id,
        failure_message
    ) VALUES (
        NULL,
        @collection_set_id,
        GETDATE(),
        NULL,
        NULL,
        NULL,
        0, -- Running
        @operator,
        NULL,
        NULL,
        NULL
    )

    SET @log_id = SCOPE_IDENTITY()                
    
    RETURN (0)
END
go

PRINT ''
PRINT 'Creating stored procedure sp_syscollector_event_oncollectionstop...'

IF (NOT OBJECT_ID('dbo.sp_syscollector_event_oncollectionstop', 'P') IS NULL)
    DROP PROCEDURE [dbo].[sp_syscollector_event_oncollectionstop]
go

CREATE PROCEDURE [dbo].[sp_syscollector_event_oncollectionstop]
    @collection_set_id int
AS
BEGIN
    SET NOCOUNT ON

    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_proxy'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_proxy')
        RETURN(1) -- Failure
    END

    -- Check the collection_set_id
    IF (@collection_set_id IS NULL)
    BEGIN
        RAISERROR(14606, -1, -1, '@collection_set_id')
        RETURN (1)
    END
    ELSE IF (NOT EXISTS (SELECT collection_set_id FROM dbo.syscollector_collection_sets WHERE collection_set_id = @collection_set_id))
    BEGIN
        DECLARE @collection_set_id_as_char VARCHAR(36)
        SELECT @collection_set_id_as_char = CONVERT(VARCHAR(36), @collection_set_id)

        RAISERROR(14262, -1, -1, '@collection_set_id', @collection_set_id_as_char)
        RETURN (1)
    END

    -- Find the log_id
    -- It will be a log entry for the same collection set, with no parent and not finished
    DECLARE @log_id bigint
    SELECT TOP 1 @log_id = log_id FROM dbo.syscollector_execution_log_internal 
        WHERE collection_set_id = @collection_set_id 
        AND parent_log_id IS NULL
        AND finish_time IS NULL
        ORDER BY start_time DESC

    IF (@log_id IS NULL)
    BEGIN
        -- Raise a warning message
        RAISERROR(14606, 9, -1, '@log_id')
    END
    ELSE
    BEGIN
        -- Mark the log as finished
        UPDATE dbo.syscollector_execution_log_internal SET
            finish_time = GETDATE(),
            [status] = CASE
                WHEN [status] = 0 THEN 1 -- Mark complete if it was running
                ELSE [status]            -- Leave the error status unchanged
            END
        WHERE log_id = @log_id
    END

    RETURN (0)
END
go


PRINT ''
PRINT 'Creating stored procedure sp_syscollector_event_oncollectionbegin...'

IF (NOT OBJECT_ID('dbo.sp_syscollector_event_oncollectionbegin', 'P') IS NULL)
    DROP PROCEDURE [dbo].[sp_syscollector_event_oncollectionbegin]
go

CREATE PROCEDURE [dbo].[sp_syscollector_event_oncollectionbegin]
    @collection_set_id int,
    @mode smallint = NULL,
    @operator nvarchar(128) = NULL,
    @log_id bigint OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_proxy'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_proxy')
        RETURN(1) -- Failure
    END

    -- Verify parameters
    --

    -- Check the collection_set_id
    IF (@collection_set_id IS NULL)
    BEGIN
        RAISERROR(14606, -1, -1, '@collection_set_id')
        RETURN (1)
    END
    ELSE IF (NOT EXISTS (SELECT collection_set_id FROM dbo.syscollector_collection_sets WHERE collection_set_id = @collection_set_id))
    BEGIN
        DECLARE @collection_set_id_as_char VARCHAR(36)
        SELECT @collection_set_id_as_char = CONVERT(VARCHAR(36), @collection_set_id)

        RAISERROR(14262, -1, -1, '@collection_set_id', @collection_set_id_as_char)
        RETURN (1)
    END


    -- Default operator to currently logged in user
    SET @operator = NULLIF(LTRIM(RTRIM(@operator)), '')
    SET @operator = ISNULL(@operator, suser_sname())

    -- Default mode to Collection
    SET @mode = ISNULL(@mode, 0)

    -- Find the parent log id.
    -- It will be a log entry for the same collection set, with no parent and not finished
    DECLARE @parent_log_id bigint
    SELECT TOP 1 @parent_log_id = log_id FROM dbo.syscollector_execution_log_internal 
        WHERE collection_set_id = @collection_set_id 
        AND parent_log_id IS NULL
        AND (@mode = 1 OR finish_time IS NULL)
        ORDER BY start_time DESC

    -- Insert the log record
    --
    INSERT INTO dbo.syscollector_execution_log_internal (
        parent_log_id, 
        collection_set_id, 
        collection_item_id,
        start_time,
        last_iteration_time,
        finish_time,
        runtime_execution_mode,
        [status],
        operator,
        package_id,
        package_execution_id,
        failure_message
    ) VALUES (
        @parent_log_id,
        @collection_set_id,
        NULL,
        GETDATE(),
        NULL,
        NULL,
        @mode,
        0, -- Running
        @operator,
        NULL,
        NULL,
        NULL
    )

    SET @log_id = SCOPE_IDENTITY()                
    
    RETURN (0)
END
go

PRINT ''
PRINT 'Creating stored procedure sp_syscollector_event_oncollectionend...'

IF (NOT OBJECT_ID('dbo.sp_syscollector_event_oncollectionend', 'P') IS NULL)
    DROP PROCEDURE [dbo].[sp_syscollector_event_oncollectionend]
go

CREATE PROCEDURE [dbo].[sp_syscollector_event_oncollectionend]
    @log_id bigint
AS
BEGIN
    SET NOCOUNT ON

    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_proxy'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_proxy')
        RETURN(1) -- Failure
    END

    -- Check the log_id
    DECLARE @retVal INT
    EXEC @retVal = dbo.sp_syscollector_verify_event_log_id @log_id, 1
    IF (@retVal <> 0)
        RETURN (@retVal)

    -- Mark the log as finished
    UPDATE dbo.syscollector_execution_log_internal SET
        finish_time = GETDATE(),
        [status] = CASE
            WHEN [status] = 0 THEN 1 -- Mark complete if it was running
            ELSE [status]            -- Leave the error status unchanged
        END
    WHERE log_id = @log_id

    RETURN (0)
END
go

PRINT ''
PRINT 'Creating stored procedure sp_syscollector_event_onpackagebegin...'

IF (NOT OBJECT_ID('dbo.sp_syscollector_event_onpackagebegin', 'P') IS NULL)
    DROP PROCEDURE [dbo].[sp_syscollector_event_onpackagebegin]
go

CREATE PROCEDURE [dbo].[sp_syscollector_event_onpackagebegin]
    @parent_log_id bigint,
    @package_id uniqueidentifier,
    @package_execution_id uniqueidentifier,
    @collection_item_id int = NULL,
    @mode smallint = NULL,
    @operator nvarchar(128) = NULL,
    @log_id bigint OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_proxy'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_proxy')
        RETURN(1) -- Failure
    END

    -- Verify parameters
    --

    -- Check the @parent_log_id
    IF (@parent_log_id IS NULL)
    BEGIN
        RAISERROR(14606, -1, -1, '@parent_log_id')
        RETURN (1)
    END
    ELSE IF (NOT EXISTS (SELECT log_id FROM dbo.syscollector_execution_log WHERE log_id = @parent_log_id))
    BEGIN
        DECLARE @parent_log_id_as_char VARCHAR(36)
        SELECT @parent_log_id_as_char = CONVERT(VARCHAR(36), @parent_log_id)

        RAISERROR(14262, -1, -1, '@parent_log_id', @parent_log_id_as_char)
        RETURN (1)
    END

    -- Check the @package_id
    IF (@package_id IS NULL)
    BEGIN
        RAISERROR(14606, -1, -1, '@package_id')
        RETURN (1)
    END
    -- The 84CEC861... package is an id of our special Master package that is allowed to start 
    -- the log without being saved to sysssispackages
    ELSE IF (@package_id != N'84CEC861-D619-433D-86FB-0BB851AF454A' AND NOT EXISTS (SELECT id FROM dbo.sysssispackages WHERE id = @package_id))
    BEGIN
        DECLARE @package_id_as_char VARCHAR(50)
        SELECT @package_id_as_char = CONVERT(VARCHAR(50), @package_id)

        RAISERROR(14262, -1, -1, '@package_id', @package_id_as_char)
        RETURN (1)
    END

    -- Default operator to currently logged in user
    SET @operator = NULLIF(LTRIM(RTRIM(@operator)), '')
    SET @operator = ISNULL(@operator, suser_sname())

    -- Default mode to Collection
    SET @mode = ISNULL(@mode, 0)

    -- Find out the collection_set_id from the parent
    DECLARE @collection_set_id INT
    SELECT @collection_set_id = collection_set_id FROM dbo.syscollector_execution_log WHERE log_id = @parent_log_id

    -- Check the @package_execution_id
    IF (@package_execution_id IS NULL)
    BEGIN
        RAISERROR(14606, -1, -1, '@package_execution_id')
        RETURN (1)
    END
    

    -- Insert the log record
    --
    INSERT INTO dbo.syscollector_execution_log_internal (
        parent_log_id, 
        collection_set_id, 
        collection_item_id,
        start_time,
        last_iteration_time,
        finish_time,
        runtime_execution_mode,
        [status],
        operator,
        package_id,
        package_execution_id,
        failure_message
    ) VALUES (
        @parent_log_id,
        @collection_set_id,
        @collection_item_id,        
        GETDATE(),
        NULL,
        NULL,
        @mode,
        0, -- Running
        @operator,
        @package_id,
        @package_execution_id,        
        NULL
    )

    SET @log_id = SCOPE_IDENTITY()                

    RETURN (0)
END
go


PRINT ''
PRINT 'Creating stored procedure sp_syscollector_event_onpackageend...'

IF (NOT OBJECT_ID('dbo.sp_syscollector_event_onpackageend', 'P') IS NULL)
    DROP PROCEDURE [dbo].[sp_syscollector_event_onpackageend]
go

CREATE PROCEDURE [dbo].[sp_syscollector_event_onpackageend]
    @log_id bigint
AS
BEGIN
    SET NOCOUNT ON

    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_proxy'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_proxy')
        RETURN(1) -- Failure
    END

    -- Check the log_id
    DECLARE @retVal INT
    EXEC @retVal = dbo.sp_syscollector_verify_event_log_id @log_id
    IF (@retVal <> 0)
        RETURN (@retVal)

    -- Mark the log as finished
    UPDATE dbo.syscollector_execution_log_internal SET
        finish_time = GETDATE(),
        [status] = CASE
            WHEN [status] = 0 THEN 1 -- Mark complete if it was running
            ELSE [status]            -- Leave the error status unchanged
        END
    WHERE log_id = @log_id

    DECLARE @runtime_execution_mode smallint
    DECLARE @status smallint
    SELECT @status = [status], @runtime_execution_mode = runtime_execution_mode
    FROM dbo.syscollector_execution_log_internal
    WHERE log_id = @log_id

    -- status was successful and this is logged by an upload package
    IF @status = 1 AND @runtime_execution_mode = 1
    BEGIN
        -- if the package ended succesfully, update the top most log to warning if it had failure
        -- this is because if there were a previous upload failure but the latest upload were successful, 
        -- we want indicated a warning rather than a failure throughout the lifetime of this collection set
        DECLARE @parent_log_id BIGINT
        SELECT @parent_log_id = parent_log_id FROM dbo.syscollector_execution_log_internal WHERE log_id = @log_id;
        WHILE @parent_log_id IS NOT NULL
        BEGIN
            -- get the next parent
            SET @log_id = @parent_log_id
            SELECT @parent_log_id = parent_log_id FROM dbo.syscollector_execution_log_internal WHERE log_id = @log_id;
        END

        UPDATE dbo.syscollector_execution_log_internal SET
            [status] = CASE
                WHEN [status] = 2 THEN 3 -- Mark warning if it indicated a failure
                ELSE [status]            -- Leave the original status unchanged
            END
        WHERE
            log_id = @log_id
    END

    RETURN (0)
END
go


PRINT ''
PRINT 'Creating stored procedure sp_syscollector_event_onpackageupdate...'

IF (NOT OBJECT_ID('dbo.sp_syscollector_event_onpackageupdate', 'P') IS NULL)
    DROP PROCEDURE [dbo].[sp_syscollector_event_onpackageupdate]
go

CREATE PROCEDURE [dbo].[sp_syscollector_event_onpackageupdate]
    @log_id bigint
AS
BEGIN
    SET NOCOUNT ON

    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_proxy'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_proxy')
        RETURN(1) -- Failure
    END

    -- Check the log_id
    DECLARE @retVal INT
    EXEC @retVal = dbo.sp_syscollector_verify_event_log_id @log_id
    IF (@retVal <> 0)
        RETURN (@retVal)

    -- Update the log
    UPDATE dbo.syscollector_execution_log_internal SET
        last_iteration_time = GETDATE()
    WHERE log_id = @log_id

    RETURN (0)
END
go


PRINT ''
PRINT 'Creating stored procedure sp_syscollector_event_onerror...'

IF (NOT OBJECT_ID('dbo.sp_syscollector_event_onerror', 'P') IS NULL)
    DROP PROCEDURE [dbo].[sp_syscollector_event_onerror]
go

CREATE PROCEDURE [dbo].[sp_syscollector_event_onerror]
    @log_id bigint,
    @message nvarchar(2048) = NULL
AS
BEGIN
    SET NOCOUNT ON
    
    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_proxy'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_proxy')
        RETURN(1) -- Failure
    END

    DECLARE @TranCounter INT
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_event_onerror
    ELSE
        BEGIN TRANSACTION
    
    BEGIN TRY
    -- Check the log_id
    -- If @message is passed, we can allow to enter the error for a collection set
    -- otherwise we will rely on the entries in sysssislog table to get the error message.
    DECLARE @retVal INT
    IF (@message IS NULL)
    BEGIN
        EXEC @retVal = dbo.sp_syscollector_verify_event_log_id @log_id, 0
    END
    ELSE
    BEGIN
        EXEC @retVal = dbo.sp_syscollector_verify_event_log_id @log_id, 1
    END
    IF (@retVal <> 0)
        RETURN (@retVal)


    DECLARE
         @failure_message   NVARCHAR(2048)
        ,@execution_id        UNIQUEIDENTIFIER

    IF @message IS NULL 
    BEGIN
        -- If no message is provided, find the last task that has failed
        -- for this package in the sysssislog table.
        -- Store the message as the failure_message for our package log.
        SELECT 
            @execution_id = package_execution_id
        FROM dbo.syscollector_execution_log
        WHERE log_id = @log_id

        SELECT TOP 1 
            @failure_message = [message]
        FROM dbo.sysssislog
        WHERE executionid = @execution_id
            AND (UPPER([event] COLLATE SQL_Latin1_General_CP1_CS_AS) = 'ONERROR')
        ORDER BY endtime DESC
    END 
    ELSE 
    BEGIN
        -- Otherwise use the provided message
        SET @failure_message = @message
    END

    -- Update the execution log
    UPDATE dbo.syscollector_execution_log_internal SET
         [status] = 2                    -- Mark as Failed
        ,failure_message = @failure_message
    WHERE
        log_id = @log_id

    -- Update all parent logs with the failure status
    SELECT @log_id = parent_log_id FROM dbo.syscollector_execution_log_internal WHERE log_id = @log_id;
    WHILE @log_id IS NOT NULL
    BEGIN
        UPDATE dbo.syscollector_execution_log_internal SET
            [status] = 2                    -- Mark as Failed
        WHERE
            log_id = @log_id;

        -- get the next parent
        SELECT @log_id = parent_log_id FROM dbo.syscollector_execution_log_internal WHERE log_id = @log_id;
    END

    IF (@TranCounter = 0)
        COMMIT TRANSACTION
    RETURN (0)

    END TRY
    BEGIN CATCH
        IF (@TranCounter = 0 OR XACT_STATE() = -1)
            ROLLBACK TRANSACTION
        ELSE IF (XACT_STATE() = 1)
            ROLLBACK TRANSACTION tran_event_onerror

        DECLARE @ErrorMessage   NVARCHAR(4000);
        DECLARE @ErrorSeverity  INT;
        DECLARE @ErrorState     INT;
        DECLARE @ErrorNumber    INT;
        DECLARE @ErrorLine      INT;
        DECLARE @ErrorProcedure NVARCHAR(200);
        SELECT @ErrorLine = ERROR_LINE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE(),
               @ErrorNumber = ERROR_NUMBER(),
               @ErrorMessage = ERROR_MESSAGE(),
               @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
        
        RETURN (1)    
    END CATCH
END
go
  
  
PRINT ''
PRINT 'Creating stored procedure sp_syscollector_event_onstatsupdate...'

IF (NOT OBJECT_ID('dbo.sp_syscollector_event_onstatsupdate', 'P') IS NULL)
    DROP PROCEDURE [dbo].[sp_syscollector_event_onstatsupdate]
go

CREATE PROCEDURE [dbo].[sp_syscollector_event_onstatsupdate]
    @log_id bigint,
    @task_name nvarchar(128),
    @row_count_in int = NULL,
    @row_count_out int = NULL,
    @row_count_error int = NULL,
    @execution_time_ms int = NULL
AS
BEGIN
    SET NOCOUNT ON

    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_proxy'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_proxy')
        RETURN(1) -- Failure
    END

    -- Check the log_id
    DECLARE @retVal INT
    EXEC @retVal = dbo.sp_syscollector_verify_event_log_id @log_id
    IF (@retVal <> 0)
        RETURN (@retVal)
    
    -- Check task name
    IF (@task_name IS NOT NULL)
    BEGIN
        SET @task_name = NULLIF(LTRIM(RTRIM(@task_name)), N'')
    END
    IF (@task_name IS NULL)
    BEGIN
        RAISERROR(14606, -1, -1, '@task_name')
        RETURN (1)
    END
    
    -- Insert the log entry
    INSERT INTO dbo.syscollector_execution_stats_internal (
        log_id,
        task_name,
        execution_row_count_in,
        execution_row_count_out,
        execution_row_count_errors,
        execution_time_ms,
        log_time
    ) VALUES (
        @log_id,
        @task_name,
        @row_count_in,
        @row_count_out,
        @row_count_error,
        NULLIF(@execution_time_ms, 0),
        GETDATE()
    )

    RETURN (0)
END
go

---------------------------------------------------------------
-- Data Collector log viewing functions and views
---------------------------------------------------------------

-- [fn_syscollector_find_collection_set_root]
-- This function finds the first log entry for a given collection set
-- run. It retunrs log_id of that entry. 
PRINT ''
PRINT 'Creating function [dbo].[fn_syscollector_find_collection_set_root] ...'
IF (NOT OBJECT_ID(N'dbo.fn_syscollector_find_collection_set_root', 'FN') IS NULL)
  DROP FUNCTION [dbo].[fn_syscollector_find_collection_set_root]
go

CREATE FUNCTION [dbo].[fn_syscollector_find_collection_set_root]
(
    @log_id BIGINT
)
RETURNS BIGINT
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    DECLARE @root_id BIGINT;

    -- Derive result using a CTE as the table is self-referencing
    WITH graph AS
    (
        -- select the anchor (specified) node
        SELECT log_id, parent_log_id FROM dbo.syscollector_execution_log WHERE log_id = @log_id
        UNION ALL
        -- select the parent node recursively
        SELECT node.log_id, node.parent_log_id FROM dbo.syscollector_execution_log node
        INNER JOIN graph AS leaf ON (node.log_id = leaf.parent_log_id)
    )
    SELECT @root_id = log_id FROM graph WHERE parent_log_id = 0;
    
    --Return result
    RETURN ISNULL(@root_id, @log_id)
END 
go

-- [fn_syscollector_get_execution_log_tree]
-- This function returns a set of log entries related to a single run 
-- of a collection set. The entries are ordered in a hierarchy, starting from
-- the collection set first log entry and then down through all packages 
-- that were executed as part of the collection set.
PRINT ''
PRINT 'Creating function [dbo].[fn_syscollector_get_execution_log_tree] ...'
IF (NOT OBJECT_ID(N'dbo.fn_syscollector_get_execution_log_tree', 'IF') IS NULL)
    DROP FUNCTION [dbo].[fn_syscollector_get_execution_log_tree]
go

CREATE FUNCTION [dbo].[fn_syscollector_get_execution_log_tree] 
(
     @log_id                BIGINT,
     @from_collection_set    BIT = 1
) 
RETURNS TABLE
AS
RETURN
(
    -- Derive result using a CTE as the table is self-referencing
    WITH graph AS 
    (
        -- select the anchor (specified) node
        SELECT 
            log_id,
            parent_log_id,
            collection_set_id,
            start_time,
            last_iteration_time,
            finish_time,
            runtime_execution_mode,
            operator,
            [status],
            package_id,
            package_execution_id,
            failure_message,
            0 AS depth 
        FROM dbo.syscollector_execution_log
        WHERE log_id = CASE @from_collection_set
            WHEN 1 THEN dbo.fn_syscollector_find_collection_set_root(@log_id)
            ELSE @log_id
        END 
        -- select the child nodes recursively
        UNION ALL
        SELECT 
            leaf.log_id,
            leaf.parent_log_id,
            leaf.collection_set_id,
            leaf.start_time,
            leaf.last_iteration_time,
            leaf.finish_time,
            leaf.runtime_execution_mode,
            leaf.operator,
            leaf.[status],
            leaf.package_id,
            leaf.package_execution_id,
            leaf.failure_message,
            node.depth + 1 AS depth
        FROM dbo.syscollector_execution_log AS leaf
        INNER JOIN graph AS node ON (node.log_id = leaf.parent_log_id)
    )
    SELECT 
        log_id,
        parent_log_id,
        collection_set_id,
        start_time,
        last_iteration_time,
        finish_time,
        CASE 
            WHEN finish_time IS NOT NULL THEN DATEDIFF(ss, start_time, finish_time) 
            WHEN last_iteration_time IS NOT NULL THEN DATEDIFF(ss, start_time, last_iteration_time) 
            ELSE 0
        END AS duration,
        runtime_execution_mode,
        operator,
        [status],
        package_id,
        package_execution_id,
        failure_message,
        depth 
    FROM graph
) 
go


-- [fn_syscollector_get_execution_stats]
-- This function returns stats for each execution of a package.
-- The stats should be logged for each iteration within the package.
PRINT ''
PRINT 'Creating function [dbo].[fn_syscollector_get_execution_stats] ...'
IF (NOT OBJECT_ID(N'dbo.fn_syscollector_get_execution_stats', 'IF') IS NULL)
    DROP FUNCTION [dbo].[fn_syscollector_get_execution_stats]
go

CREATE FUNCTION [dbo].[fn_syscollector_get_execution_stats] 
(
     @log_id                BIGINT
) 
RETURNS TABLE
AS
RETURN
(
    SELECT 
        log_id,
        task_name,
        AVG(execution_row_count_in) AS avg_row_count_in,
        MIN(execution_row_count_in) AS min_row_count_in,
        MAX(execution_row_count_in) AS max_row_count_in,
        AVG(execution_row_count_out) AS avg_row_count_out,
        MIN(execution_row_count_out) AS min_row_count_out,
        MAX(execution_row_count_out) AS max_row_count_out,
        AVG(execution_row_count_errors) AS avg_row_count_errors,
        MIN(execution_row_count_errors) AS min_row_count_errors,
        MAX(execution_row_count_errors) AS max_row_count_errors,
        AVG(execution_time_ms) AS avg_duration,
        MIN(execution_time_ms) AS min_duration,
        MAX(execution_time_ms) AS max_duration
    FROM dbo.syscollector_execution_stats
    WHERE log_id = @log_id
    GROUP BY log_id, task_name
)
go


-- [fn_syscollector_get_execution_details]
-- This function returns detailed log entries from SSIS log for each package
-- execution. The log id passed as input is an id of a log entry for that package
-- from syscollector_execution_log view.
PRINT ''
PRINT 'Creating function [dbo].[fn_syscollector_get_execution_details] ...'
IF (NOT OBJECT_ID(N'dbo.fn_syscollector_get_execution_details', 'IF') IS NULL)
    DROP FUNCTION [dbo].[fn_syscollector_get_execution_details]
go
CREATE FUNCTION [dbo].[fn_syscollector_get_execution_details] 
(
     @log_id                BIGINT
)
RETURNS TABLE
AS
RETURN
(
    SELECT TOP (100) PERCENT
        l.source,
        l.event,
        l.message,
        l.starttime AS start_time,
        l.endtime AS finish_time,
        l.datacode,
        l.databytes
    FROM sysssislog l
    JOIN dbo.syscollector_execution_log e ON (e.package_execution_id = l.executionid)
    WHERE e.log_id = @log_id
    ORDER BY l.starttime
)
go            


-- [syscollector_execution_log_full]
-- An expanded log view that shows the log entries in a hierarchical order.
PRINT ''
PRINT 'Creating view syscollector_execution_log_full...'
IF (NOT OBJECT_ID('dbo.syscollector_execution_log_full', 'V') IS NULL)
    DROP VIEW [dbo].[syscollector_execution_log_full]
go

CREATE VIEW [dbo].[syscollector_execution_log_full]
AS
SELECT 
        t.log_id,
        ISNULL(t.parent_log_id, 0) as parent_log_id,
        CASE 
            WHEN t.package_id IS NULL THEN SPACE(t.depth * 4) + c.name
            WHEN t.package_id = N'84CEC861-D619-433D-86FB-0BB851AF454A' THEN SPACE(t.depth * 4) + N'Master'
            ELSE SPACE(t.depth * 4) + p.name 
        END AS [name],
        t.[status],
        t.runtime_execution_mode,
        t.start_time,
        t.last_iteration_time,
        t.finish_time,
        t.duration,
        t.failure_message,
        t.operator,
        t.package_execution_id,
        t.collection_set_id
    FROM dbo.syscollector_execution_log_internal l
    CROSS APPLY dbo.fn_syscollector_get_execution_log_tree(l.log_id, 0) t
    LEFT OUTER JOIN dbo.syscollector_collection_sets c ON( c.collection_set_id = t.collection_set_id)
    LEFT OUTER JOIN dbo.sysssispackages p ON (p.id = t.package_id AND p.id != N'84CEC861-D619-433D-86FB-0BB851AF454A')
    WHERE l.parent_log_id IS NULL
go
        
---------------------------------------------------------------
-- Data Collection log clean-up
---------------------------------------------------------------

-- [sp_syscollector_delete_execution_log_tree]
-- This stored procedure removes all log entries related to a single
-- run of a collection set. It also removes corresponding log entries
-- from SSIS log tables.
PRINT ''
IF (NOT OBJECT_ID(N'dbo.sp_syscollector_delete_execution_log_tree', 'P') IS NULL)
BEGIN
  PRINT 'Dropping procedure [dbo].[sp_syscollector_delete_execution_log_tree] ...'
  DROP PROCEDURE [dbo].[sp_syscollector_delete_execution_log_tree]
END
go

PRINT 'Creating procedure [dbo].[sp_syscollector_delete_execution_log_tree] ...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_delete_execution_log_tree]
    @log_id BIGINT,
    @from_collection_set    BIT = 1
AS
BEGIN
    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_operator'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14677, -1, -1, 'dc_operator')
        RETURN(1) -- Failure
    END

    SET NOCOUNT ON;
