    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;
    CREATE TABLE #log_ids (log_id BIGINT);
    
    WITH graph AS
    (
        SELECT log_id 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
        UNION ALL
        SELECT leaf.log_id FROM dbo.syscollector_execution_log AS leaf
        INNER JOIN graph AS node ON (node.log_id = leaf.parent_log_id)
    )
    INSERT INTO #log_ids
    SELECT log_id
    FROM graph
    
    -- Delete all ssis log records pertaining to the selected logs
    DELETE FROM dbo.sysssislog
        FROM dbo.sysssislog AS s
        INNER JOIN dbo.syscollector_execution_log_internal AS l ON (l.package_execution_id = s.executionid)
        INNER JOIN #log_ids AS i ON i.log_id = l.log_id
        
    -- Then delete the actual logs
    DELETE FROM syscollector_execution_log_internal
        FROM syscollector_execution_log_internal AS l
        INNER Join #log_ids AS i ON i.log_id = l.log_id

    DROP TABLE #log_ids
    RETURN (0)
END
GO

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

PRINT 'Creating procedure [dbo].[sp_syscollector_delete_collection_set_internal]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_delete_collection_set_internal]
    @collection_set_id      int,
    @name                   sysname,
    @collection_job_id      uniqueidentifier,
    @upload_job_id          uniqueidentifier,
    @collection_mode        smallint
AS
BEGIN
    DECLARE @TranCounter int
    SET @TranCounter = @@TRANCOUNT
    IF (@TranCounter > 0)
        SAVE TRANSACTION tran_delete_collection_set
    ELSE
        BEGIN TRANSACTION
    
    BEGIN TRY
        -- clean log before deleting collection set
        DECLARE @log_id bigint
        SET @log_id = (SELECT TOP(1) log_id  FROM dbo.syscollector_execution_log WHERE collection_set_id = @collection_set_id)
        WHILE (@log_id IS NOT NULL)
        BEGIN
            EXEC dbo.sp_syscollector_delete_execution_log_tree @log_id = @log_id
            SET @log_id = (SELECT TOP(1) log_id  FROM dbo.syscollector_execution_log WHERE collection_set_id = @collection_set_id)
        END

        DECLARE @schedule_id    int
        SELECT @schedule_id = schedule_id
        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

        DELETE [dbo].[syscollector_collection_sets_internal]
        WHERE collection_set_id = @collection_set_id

        EXEC dbo.sp_syscollector_delete_jobs 
            @collection_job_id        = @collection_job_id,
            @upload_job_id            = @upload_job_id,
            @schedule_id            = @schedule_id,
            @collection_mode        = @collection_mode

        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_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

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

PRINT 'Creating procedure [dbo].[sp_syscollector_delete_collection_set]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_delete_collection_set]
    @collection_set_id            int = NULL,
    @name                        sysname = NULL
WITH EXECUTE AS OWNER -- 'MS_DataCollectorInternalUser'
AS
BEGIN
    -- 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;

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

    DECLARE @is_system            bit
    DECLARE @is_running            bit
    DECLARE @upload_job_id        uniqueidentifier
    DECLARE @collection_job_id    uniqueidentifier
    DECLARE @collection_mode    smallint
    SELECT    @is_running = is_running,
            @is_system = is_system,
            @upload_job_id = upload_job_id, 
            @collection_job_id = collection_job_id,
            @collection_mode = collection_mode
    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

    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 checks are go
    -- Do the actual delete
    EXEC @retVal = sp_syscollector_delete_collection_set_internal
                        @collection_set_id = @collection_set_id, 
                        @name = @name,
                        @collection_job_id = @collection_job_id,
                        @upload_job_id = @upload_job_id,
                        @collection_mode = @collection_mode
    RETURN (0)
END
GO

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

-- [sp_syscollector_purge_collection_logs]
-- The sp cleans any log record with an expired finish date
-- Expiration is measured from the date provided to the sp
-- and defaults to TODAY.
PRINT ''
PRINT 'Creating stored procedure sp_syscollector_purge_collection_logs...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_purge_collection_logs]
    @reference_date datetime = 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

    IF (@reference_date IS NULL)
    BEGIN
        SET @reference_date = GETDATE()
    END
    
    -- An expired log record is any record of a collection set that is older than 
    -- the reference date minus the collection set's days_until_expiration
    CREATE TABLE #purged_log_ids (log_id BIGINT)
    
    INSERT INTO #purged_log_ids
    SELECT log_id
    FROM syscollector_execution_log_internal as l
    INNER JOIN syscollector_collection_sets s ON l.collection_set_id = s.collection_set_id
    WHERE s.days_until_expiration > 0
    AND @reference_date >= DATEADD(DAY, s.days_until_expiration, l.finish_time)

    -- Delete all ssis log records pertaining to expired logs
    DELETE FROM dbo.sysssislog
        FROM dbo.sysssislog AS s
        INNER JOIN dbo.syscollector_execution_log_internal AS l ON (l.package_execution_id = s.executionid)
        INNER JOIN #purged_log_ids AS i ON i.log_id = l.log_id
        
    -- Then delete the actual logs
    DELETE FROM syscollector_execution_log_internal
        FROM syscollector_execution_log_internal AS l
        INNER Join #purged_log_ids AS i ON i.log_id = l.log_id

    DROP TABLE #purged_log_ids
    -- Go for another round to cleanup the orphans
    -- Ideally, the log heirarchy guarantees that a finish time by a parent log will always
    -- be higher than the finish time of any of its descendants.
    -- The purge step however does not delete log records with a null finish time
    -- A child log can have a null finish time while its parent is closed if there is an
    -- error in execution that causes the log to stay open.
    -- If such a child log exists, its parent will be purged leaving it as an orphan
    
    -- get orphan records and all their descendants in a cursor and purge them
    DECLARE orphaned_log_cursor INSENSITIVE CURSOR FOR
            SELECT log_id 
            FROM syscollector_execution_log_internal
            WHERE parent_log_id NOT IN (
                SELECT log_id FROM syscollector_execution_log_internal
            )
            FOR READ ONLY
            
    DECLARE @log_id BIGINT

    -- for every orphan, delete all its remaining tree
    -- this is supposedly a very small fraction of the entire log
    OPEN orphaned_log_cursor    
    FETCH orphaned_log_cursor INTO @log_id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC sp_syscollector_delete_execution_log_tree @log_id = @log_id, @from_collection_set = 0
        FETCH orphaned_log_cursor INTO @log_id
    END
    
    CLOSE orphaned_log_cursor
    DEALLOCATE orphaned_log_cursor
END
GO

---------------------------------------------------------------
-- Start and stop Data Collector
---------------------------------------------------------------

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

PRINT 'Creating procedure [dbo].[sp_syscollector_enable_collector]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_enable_collector]
WITH EXECUTE AS OWNER -- 'MS_DataCollectorInternalUser'
AS
BEGIN
    -- 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;

    BEGIN TRANSACTION

    DECLARE @was_enabled int;

    SELECT @was_enabled = ISNULL(CONVERT(int, parameter_value),0)
    FROM [dbo].[syscollector_config_store_internal]
    WHERE parameter_name = 'CollectorEnabled'

    IF (@was_enabled = 0)
    BEGIN

        UPDATE [dbo].[syscollector_config_store_internal]
        SET parameter_value = 1
        WHERE parameter_name = 'CollectorEnabled'

        DECLARE @collection_set_id int

        DECLARE collection_set_cursor CURSOR LOCAL FOR
            SELECT collection_set_id
            FROM dbo.syscollector_collection_sets
            WHERE is_running = 1

        OPEN collection_set_cursor
        FETCH collection_set_cursor INTO @collection_set_id

        WHILE @@FETCH_STATUS = 0 
        BEGIN
            EXEC dbo.sp_syscollector_start_collection_set_jobs @collection_set_id = @collection_set_id
            FETCH collection_set_cursor INTO @collection_set_id
        END

        CLOSE collection_set_cursor
        DEALLOCATE collection_set_cursor

    END

    COMMIT TRANSACTION

END
GO


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

PRINT 'Creating procedure [dbo].[sp_syscollector_disable_collector]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_disable_collector]
WITH EXECUTE AS OWNER -- 'MS_DataCollectorInternalUser'
AS
BEGIN
    -- 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;

    BEGIN TRANSACTION

    DECLARE @was_enabled int;

    SELECT @was_enabled = ISNULL(CONVERT(int, parameter_value),0)
    FROM [dbo].[syscollector_config_store_internal]
    WHERE parameter_name = 'CollectorEnabled'

    IF (@was_enabled <> 0)
    BEGIN

        UPDATE [dbo].[syscollector_config_store_internal]
        SET parameter_value = 0
        WHERE parameter_name = 'CollectorEnabled'

        DECLARE @collection_set_id INT
        DECLARE @collection_mode SMALLINT
        DECLARE @collection_job_id UNIQUEIDENTIFIER

        DECLARE collection_set_cursor CURSOR LOCAL FOR
            SELECT collection_set_id, collection_mode, collection_job_id
            FROM dbo.syscollector_collection_sets
            WHERE is_running = 1

        OPEN collection_set_cursor
        FETCH collection_set_cursor INTO @collection_set_id, @collection_mode, @collection_job_id

        WHILE @@FETCH_STATUS = 0 
        BEGIN
            -- If this collection set is running in cached mode, and the collection job is running, we need to stop the job explicitly here
            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    

            IF (@is_collection_job_running = 1
                AND @collection_mode = 0)           -- Cached mode
            BEGIN
                EXEC sp_stop_job @job_id = @collection_job_id
            END

            -- Now, disable the jobs and detach them from the upload schedules
            EXEC dbo.sp_syscollector_stop_collection_set_jobs @collection_set_id = @collection_set_id
            FETCH collection_set_cursor INTO @collection_set_id, @collection_mode, @collection_job_id
        END
        CLOSE collection_set_cursor
        DEALLOCATE collection_set_cursor

    END

    COMMIT TRANSACTION

END
GO

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

PRINT 'Creating procedure [dbo].[sp_syscollector_get_trace_info]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_get_trace_info]
    @trace_path  nvarchar(512),
    @use_default int
AS
BEGIN
    SELECT 
        CONVERT(nvarchar(30), t.start_time, 126) as start_time,
        CASE t.status 
            WHEN 1 THEN 1 
            ELSE 0 
        END AS is_running, 
        ISNULL(t.dropped_event_count,0) as dropped_event_count,
        t.id
    FROM sys.traces t
    WHERE (@use_default=1 and t.is_default=1)
          OR (@use_default=0 AND t.path LIKE (@trace_path + N'%.trc'))
END
GO

---------------------------------------------------------------
-- Data Collector: Helper procedures
---------------------------------------------------------------

-- Procedure to retrieve a query plan from cache
IF (NOT OBJECT_ID('[dbo].[sp_syscollector_text_query_plan_lookpup]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_text_query_plan_lookpup]'
    DROP PROCEDURE [dbo].[sp_syscollector_text_query_plan_lookpup]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_text_query_plan_lookpup]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_text_query_plan_lookpup]
    @plan_handle varbinary(64),
    @statement_start_offset int,
    @statement_end_offset int
AS
BEGIN
    SET NOCOUNT ON
    SELECT    
        @plan_handle AS plan_handle,
        @statement_start_offset AS statement_start_offset,
        @statement_end_offset AS statement_end_offset,
        [dbid] AS database_id,
        [objectid] AS object_id,
        OBJECT_NAME(objectid, dbid) AS object_name,
        [query_plan] AS query_plan
    FROM    
        [sys].[dm_exec_text_query_plan](@plan_handle, @statement_start_offset, @statement_end_offset) dm
END
GO

-- Procedure to retrieve a query text from cache
IF (NOT OBJECT_ID('[dbo].[sp_syscollector_sql_text_lookup]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_sql_text_lookup]'
    DROP PROCEDURE [dbo].[sp_syscollector_sql_text_lookup]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_sql_text_lookup]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_sql_text_lookup]
    @sql_handle varbinary(64)
AS
BEGIN
    SET NOCOUNT ON
    SELECT    
        @sql_handle as sql_handle,
        dm.[dbid] AS database_id,
        dm.[objectid] AS object_id,
        OBJECT_NAME(objectid, dbid) AS object_name,
        CASE dm.[encrypted]
            WHEN 1 THEN N'Query SQL Text Encrypted'
            ELSE dm.[text]
        END AS sql_text
        FROM    
            [sys].[dm_exec_sql_text](@sql_handle) dm
END
GO

---------------------------------------------------------------
-- Install out-of-the-box objects
---------------------------------------------------------------
PRINT 'Installing out of the box Collector objects'
PRINT ''

-- We need agent XP's to be on for many parts of the coming installation script
-- Enable them here once and return them to their original state when done

DECLARE @advopt_old_value int
DECLARE @comp_old_value int
EXEC #sp_enable_component 'Agent XPs', @advopt_old_value out, @comp_old_value out

-- We need the old values to endure beyond batches
-- insert them into temp tables

IF (OBJECT_ID('tempdb..#advopt_old_value', 'U') IS NOT NULL)
BEGIN
	DROP TABLE #advopt_old_value
END

SELECT @advopt_old_value AS advopt_old_value
INTO #advopt_old_value

IF (OBJECT_ID('tempdb..#comp_old_value', 'U') IS NOT NULL)
BEGIN
	DROP TABLE #comp_old_value
END

SELECT @comp_old_value AS comp_old_value
INTO #comp_old_value
GO

-- disable the collector first
EXEC sp_syscollector_disable_collector
GO

---------------------------------------------------------------
-- Out-of-the-box SSIS folders for Data Collector
---------------------------------------------------------------

PRINT 'Creating SSIS folders...'
-- create 'Data Collector' folder under the root
IF(NOT EXISTS(SELECT * 
                FROM dbo.sysssispackagefolders
                WHERE folderid = '8877FE4B-A938-4a51-84B9-C5BDAD74B0AD'))
BEGIN
    EXEC dbo.sp_ssis_addfolder
        @parentfolderid = '00000000-0000-0000-0000-000000000000',
        @name = 'Data Collector',
        @folderid = '8877FE4B-A938-4a51-84B9-C5BDAD74B0AD'
END
GO
-- create 'Generated' folder under 'Data Collector'
IF(NOT EXISTS(SELECT * 
                FROM dbo.sysssispackagefolders
                WHERE folderid = '39163C42-602B-42C9-B4F7-1843614F9625'))
BEGIN
    EXEC dbo.sp_ssis_addfolder
        @parentfolderid = '8877FE4B-A938-4a51-84B9-C5BDAD74B0AD',
        @name = 'Generated',
        @folderid = '39163C42-602B-42c9-B4F7-1843614F9625'
END
GO

---------------------------------------------------------------
-- Loading instmdw.sql
---------------------------------------------------------------

-- a data collector table to store BLOB
IF (OBJECT_ID(N'[dbo].[syscollector_blobs_internal]', 'U') IS NULL)
BEGIN
    PRINT 'Creating table [dbo].[syscollector_blobs_internal]...'
    CREATE TABLE [dbo].[syscollector_blobs_internal] (
        parameter_name                nvarchar(128) NOT NULL,
        parameter_value               varbinary(max) NOT NULL,
        CONSTRAINT [PK_syscollector_blobs_internal_paremeter_name] PRIMARY KEY CLUSTERED (parameter_name ASC)
        )
END
GO

-- an SP to read a parameter value from the syscollector BLOB table
-- this stored procedure is called by the wizard to retrieve the instmdw.sql when setting up MDW
IF (NOT OBJECT_ID('[dbo].[sp_syscollector_get_instmdw]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_get_instmdw]'
    DROP PROCEDURE [dbo].[sp_syscollector_get_instmdw]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_get_instmdw]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_get_instmdw]
AS
BEGIN
    -- only dc_admin and dbo can setup MDW
    IF (NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14712, -1, -1) WITH LOG
        RETURN(1) -- Failure
    END

    -- if the script has not been loaded, load it now
    IF (NOT EXISTS(SELECT parameter_name 
                   FROM syscollector_blobs_internal
                   WHERE parameter_name = N'InstMDWScript'))
    BEGIN
        EXECUTE sp_syscollector_upload_instmdw
    END
               
    SELECT cast(parameter_value as nvarchar(max)) 
    FROM syscollector_blobs_internal
    WHERE parameter_name = N'InstMDWScript'
END
GO

-- the script that would upload or update instmdw.sql
-- this is used when a hotfix, service pack, or upgrade is issued in instmdw.sql
-- user can specify the path to the new instmdw.sql to be installed or updated to.
IF (NOT OBJECT_ID('[dbo].[sp_syscollector_upload_instmdw]', 'P') IS NULL)
BEGIN
    PRINT 'Dropping procedure [dbo].[sp_syscollector_upload_instmdw]'
    DROP PROCEDURE [dbo].[sp_syscollector_upload_instmdw]
END
GO

PRINT 'Creating procedure [dbo].[sp_syscollector_upload_instmdw]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_upload_instmdw]
    @installpath              nvarchar(2048) = NULL
AS
BEGIN
    -- only dc_admin and dbo can setup MDW
    IF (NOT (ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT (ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
    BEGIN
        RAISERROR(14712, -1, -1) WITH LOG
        RETURN(1) -- Failure
    END

    IF (@installpath IS NULL)
    BEGIN
        EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @installpath OUTPUT;
    
        IF RIGHT(@installpath, 1) != N'\' set @installpath = @installpath + N'\'
        SET @installpath  = @installpath + N'Install\'
    END

    DECLARE @filename nvarchar(2048);
    SET @filename = @installpath + N'instmdw.sql'
    PRINT 'Uploading instmdw.sql from disk: ' + @filename

    CREATE TABLE #bulkuploadinstmdwscript
        (
            [instmdwscript] nvarchar(max) NOT NULL
        );


    DECLARE @stmt_bulkinsert nvarchar(2048);
    SET @stmt_bulkinsert = N'
    BULK INSERT #bulkuploadinstmdwscript
    FROM ' 
        -- Escape any embedded single quotes (we can't use QUOTENAME here b/c it can't handle strings > 128 chars)
        + '''' + REPLACE(@filename COLLATE database_default, N'''', N'''''') + '''
    WITH
        (
            DATAFILETYPE = ''char'',
            FIELDTERMINATOR = ''dc:stub:ft'',
            ROWTERMINATOR = ''dc:stub:rt'',
            CODEPAGE = ''RAW''
        );
    ';

    EXECUTE sp_executesql @stmt_bulkinsert;

    DECLARE @bytesLoaded int
    SELECT @bytesLoaded = ISNULL (DATALENGTH ([instmdwscript]), 0) FROM #bulkuploadinstmdwscript
    PRINT 'Loaded ' + CONVERT (nvarchar, @bytesLoaded)
            + ' bytes from ' + '''' + REPLACE(@filename COLLATE database_default, N'''', N'''''') + ''''

    DECLARE @scriptdata varbinary(max)
    SELECT @scriptdata = convert(varbinary(max), [instmdwscript]) FROM #bulkuploadinstmdwscript;

    IF (EXISTS(SELECT * FROM [dbo].[syscollector_blobs_internal]
        WHERE parameter_name = N'InstMDWScript'))
    BEGIN
        UPDATE [dbo].[syscollector_blobs_internal]
        SET parameter_value = @scriptdata
        WHERE parameter_name = N'InstMDWScript'
    END
    ELSE
    BEGIN
        INSERT INTO [dbo].[syscollector_blobs_internal] (
            parameter_name, 
            parameter_value
        )
        VALUES
        (
            N'InstMDWScript',
            @scriptdata
        )
    END

    DROP TABLE #bulkuploadinstmdwscript
END
GO


---------------------------------------------------------------
-- Out-of-the-box data collector packages - loading SSIS packages
---------------------------------------------------------------

CREATE PROCEDURE #syscollector_upload_package_from_file 
    @filename nvarchar(2048),
    @packagename sysname,
    @packageid uniqueidentifier,
    @versionid uniqueidentifier
AS
BEGIN
    RAISERROR ('Uploading data collector package from disk: %s', 0, 1, @filename) WITH NOWAIT;

    CREATE TABLE #bulkpackage
        (
            [packagexml] xml NOT NULL
        );

    DECLARE @stmt_bulkinsert nvarchar(2048);
    SET @stmt_bulkinsert = N'
    BULK INSERT #bulkpackage
    FROM ' 
    -- Escape any embedded single quotes (we can't use QUOTENAME here b/c it can't handle strings > 128 chars)
    + '''' + REPLACE(@filename COLLATE database_default, N'''', N'''''') + '''
    WITH
        (
            DATAFILETYPE = ''char'',
            FIELDTERMINATOR = ''dc:stub:ft'',
            ROWTERMINATOR = ''dc:stub:rt'',
            CODEPAGE = ''RAW''
        );
    ';

    EXECUTE sp_executesql @stmt_bulkinsert;

    DECLARE @bytesLoaded int
    SELECT @bytesLoaded = ISNULL (DATALENGTH ([packagexml]), 0) FROM #bulkpackage
    PRINT 'Loaded ' + CONVERT (varchar, @bytesLoaded)
            + ' bytes from ' 
            + '''' + REPLACE(@filename COLLATE database_default, N'''', N'''''') + ''''

    DECLARE @packagebin varbinary(max);
    SELECT @packagebin = convert(varbinary(max),[packagexml]) FROM #bulkpackage;

    DECLARE @loadtime datetime;
    SET @loadtime = getdate();

    DROP TABLE #bulkpackage

    EXECUTE sp_ssis_putpackage
            @name = @packagename
        ,    @id = @packageid
        ,    @description = N'System Data Collector Package'
        ,    @createdate = @loadtime
        ,    @folderid = '8877FE4B-A938-4a51-84B9-C5BDAD74B0AD'
        ,    @packagedata = @packagebin
        ,    @packageformat = 1
        ,    @packagetype = 5 -- DTSPKT_DTSDESIGNER100
        ,    @vermajor = 1
        ,    @verminor = 0
        ,    @verbuild = 0
        ,    @vercomments = N''
        ,    @verid = @versionid
    ;
END;
GO

CREATE PROCEDURE #syscollector_upload_package 
    @packagename sysname,
    @packageid uniqueidentifier,
    @versionid uniqueidentifier
AS
BEGIN
    DECLARE @installpath nvarchar(2048);
    EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @installpath OUTPUT;
    IF RIGHT(@installpath,1) != N'\' set @installpath = @installpath + N'\'
    SET @installpath  = @installpath + N'Install\'

    DECLARE @filename nvarchar(2048);
    SET @filename = @installpath + @packagename + N'.dtsx'
    RAISERROR ('Uploading data collector package from disk: %s', 0, 1, @filename) WITH NOWAIT;

    EXEC #syscollector_upload_package_from_file @filename=@filename, @packagename=@packagename, @packageid=@packageid, @versionid=@versionid;    
END;
GO

--
-- Data Collector placeholder comment, dont move or remove.
-- 875f7de1-9e83-4be1-8b96-2df4a8533e88
--

-- Load SSIS packages needed by collector types

-- Temporarily enable the 'Agent XPs' config option so that sp_ssis_putpackage can 
-- succeed when SQLAgent is stopped. 

EXECUTE #syscollector_upload_package 
        @packagename='SqlTraceCollect'
    ,    @packageid='0E149FC9-1046-4DE6-98BF-4B22ED6F6C42'
    ,    @versionid='244F0904-5CC6-49B8-AE90-905AEEA8BAF3';

EXECUTE #syscollector_upload_package 
        @packagename='SqlTraceUpload'
    ,    @packageid='F389A8E6-5A17-4056-ABFD-C8B823F2092E'
    ,    @versionid='2D32AB4C-9929-4A85-A94E-7A01D8F40016';

EXECUTE #syscollector_upload_package 
        @packagename='TSQLQueryCollect'
    ,    @packageid='292B1476-0F46-4490-A9B7-6DB724DE3C0B'
    ,    @versionid='E24C6D00-94C6-457B-BED4-1F9F018F3273';

EXECUTE #syscollector_upload_package 
        @packagename='TSQLQueryUpload'
    ,    @packageid='6EB73801-39CF-489C-B682-497350C939F0'
    ,    @versionid='DA1210BC-C31B-43C6-B255-D8DDEB288CA1';

EXECUTE #syscollector_upload_package 
        @packagename='PerfCountersCollect'
    ,    @packageid='C2EAABC1-5BF3-4127-BEB3-26E94D026E7D'
    ,    @versionid='09A5B959-21B3-44E1-A37F-4A62BE5D6244';

EXECUTE #syscollector_upload_package 
        @packagename='PerfCountersUpload'
    ,    @packageid='08D854CB-0D45-4E96-92C6-227A5DCD7066'
    ,    @versionid='22A676DD-2025-493A-AD6B-C0186ABD556F';

EXECUTE #syscollector_upload_package 
        @packagename='QueryActivityCollect'
    ,    @packageid='0B68FC9D-23DC-48F3-A937-90A0A8943D0E'
    ,    @versionid='75A3A143-2059-433B-A11C-C8E0C80A83CF';

EXECUTE #syscollector_upload_package 
        @packagename='QueryActivityUpload'
    ,    @packageid='833DB628-8E19-47A3-92C5-FB1779B52E76'
    ,    @versionid='B1D79132-C6E6-46AA-8B14-E0AE4C4BA7BB';
GO

-- Cleanup the temp stored proc that we used to upload the SSIS packages
DROP PROCEDURE #syscollector_upload_package_from_file
DROP PROCEDURE #syscollector_upload_package
GO

---------------------------------------------------------------
-- Out-of-the-box collector type objects - definition for types
---------------------------------------------------------------

-- Stop system collection sets 
PRINT 'Stopping system collection sets...'

DECLARE @collection_set_id INT;
DECLARE system_collection_sets_cursor CURSOR LOCAL
    FOR SELECT collection_set_id FROM syscollector_collection_sets WHERE is_system = 1;

OPEN system_collection_sets_cursor;
FETCH system_collection_sets_cursor INTO @collection_set_id;

WHILE @@FETCH_STATUS = 0 
BEGIN
    -- stop the collection set if is running
    RAISERROR ('Stopping collection set with ID %d...', 0, 1, @collection_set_id) WITH NOWAIT
    EXEC sp_syscollector_stop_collection_set @collection_set_id = @collection_set_id
    
    FETCH system_collection_sets_cursor INTO @collection_set_id;
END

CLOSE system_collection_sets_cursor;
DEALLOCATE system_collection_sets_cursor;
GO

PRINT 'Creating or updating Collection Types...'
GO
-- Performance counters collector type
DECLARE @collector_type_uid uniqueidentifier
DECLARE @name sysname
DECLARE @parameter_schema xml
DECLARE @parameter_formatter xml
DECLARE @collection_package_id uniqueidentifier
DECLARE @upload_package_id uniqueidentifier

SET @collector_type_uid = '294605dd-21de-40b2-b20f-f3e170ea1ec3'
SET @name = 'Performance Counters Collector Type'
SET @parameter_schema =
		'<?xml version="1.0" encoding="utf-8"?>
			<xs:schema targetNamespace="DataCollectorType" xmlns:xs="http://www.w3.org/2001/XMLSchema">
			 <xs:element name="PerformanceCountersCollector">
			  <xs:complexType>
				  <xs:sequence>
					  <xs:element minOccurs="0" maxOccurs="unbounded" name="PerformanceCounters">
						  <xs:complexType>
							  <xs:attribute name="Objects" type="xs:string" use="required" />
							  <xs:attribute name="Counters" type="xs:string" use="required" />
							  <xs:attribute name="Instances" type="xs:string" use="optional" />
						  </xs:complexType>
					  </xs:element>
				  </xs:sequence>
			  	  <xs:attribute name="StoreLocalizedCounterNames" type="xs:boolean" use="optional" default="false" />
			  </xs:complexType>
			</xs:element>
			</xs:schema>
		'
SET @parameter_formatter = 
        N'<xsl:stylesheet 
            xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
            version="1.0"
            xmlns:z="#RowsetSchema"
            >
        <xsl:template match="/PerformanceCountersCollector">
            <HTML>
            <HEAD>
            <TITLE></TITLE>
            </HEAD>
            <BODY>
            <UL>
            <xsl:apply-templates select="PerformanceCounters"/>
            </UL>
            <HR/>
            </BODY>
            </HTML>
        </xsl:template>
        <xsl:template match="PerformanceCounters">
            <LI>
            \<xsl:value-of select="@Objects"/>
            <xsl:if test="@Instances">(<xsl:value-of select="@Instances"/>)</xsl:if>
            \<xsl:value-of select="@Counters"/>
            </LI>
        </xsl:template>
        </xsl:stylesheet>'
SET @collection_package_id = 'C2EAABC1-5BF3-4127-BEB3-26E94D026E7D'
SET @upload_package_id = '08D854CB-0D45-4E96-92C6-227A5DCD7066'

IF(EXISTS (SELECT * FROM dbo.syscollector_collector_types 
            WHERE collector_type_uid = @collector_type_uid))
BEGIN
    PRINT 'Updating Performance counters collector type'
    EXEC sp_syscollector_update_collector_type 
            @collector_type_uid = @collector_type_uid,
            @name = @name,
            @parameter_schema = @parameter_schema,
            @parameter_formatter = @parameter_formatter,
            @collection_package_id = @collection_package_id,
            @upload_package_id = @upload_package_id
END
ELSE
BEGIN
    PRINT 'Creating Performance Counters collector type'
    EXEC sp_syscollector_create_collector_type
            @collector_type_uid = @collector_type_uid,
            @name = @name,
            @parameter_schema = @parameter_schema,
            @parameter_formatter = @parameter_formatter,
            @collection_package_id = @collection_package_id,
            @upload_package_id = @upload_package_id
END

UPDATE syscollector_collector_types 
SET is_system = 1
WHERE collector_type_uid = @collector_type_uid
GO

-- TSQL query collector type
DECLARE @collector_type_uid uniqueidentifier
DECLARE @name sysname
DECLARE @parameter_schema xml
DECLARE @parameter_formatter xml
DECLARE @collection_package_id uniqueidentifier
DECLARE @upload_package_id uniqueidentifier

SET @collector_type_uid = '302E93D1-3424-4be7-AA8E-84813ECF2419'
SET @name = 'Generic T-SQL Query Collector Type'
SET @parameter_schema = '<?xml version="1.0" encoding="utf-8"?>
        <xs:schema targetNamespace="DataCollectorType" xmlns:xs="http://www.w3.org/2001/XMLSchema">
         <xs:element name="TSQLQueryCollector">
          <xs:complexType>
           <xs:sequence>
            <xs:element name="Query" minOccurs="1" maxOccurs="unbounded">
             <xs:complexType>
              <xs:sequence>
               <xs:element name="Value" type="xs:string" />
               <xs:element name="OutputTable" type="xs:string" />
              </xs:sequence>
             </xs:complexType>
            </xs:element>
            <xs:element name="Databases" minOccurs="0" maxOccurs="1">
             <xs:complexType>
              <xs:sequence>
               <xs:element name="Database" minOccurs="0" maxOccurs="unbounded" type="xs:string" />
              </xs:sequence>
              <xs:attribute name="UseSystemDatabases" type="xs:boolean" use="optional" />
              <xs:attribute name="UseUserDatabases" type="xs:boolean" use="optional" />
             </xs:complexType>
            </xs:element>
           </xs:sequence>
          </xs:complexType>
         </xs:element>
        </xs:schema>'
SET @parameter_formatter = 
        N'<xsl:stylesheet 
            xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
            version="1.0"
            xmlns:z="#RowsetSchema"
            >
        <xsl:template match="/TSQLQueryCollector">
            <HTML>
            <HEAD>
            <TITLE></TITLE>
            </HEAD>
            <BODY>
            <xsl:apply-templates select="Query"/>
            </BODY>
            </HTML>
        </xsl:template>
        <xsl:template match="Query">
            <I>
            <xsl:value-of select="OutputTable"/>
            </I> <BR/>
            <PRE>
            <xsl:value-of select="Value"/>
            </PRE>
            <HR/>
        </xsl:template>
        </xsl:stylesheet>'
SET @collection_package_id = '292B1476-0F46-4490-A9B7-6DB724DE3C0B'
SET @upload_package_id = '6EB73801-39CF-489C-B682-497350C939F0'

IF(EXISTS (SELECT * FROM dbo.syscollector_collector_types 
            WHERE collector_type_uid = @collector_type_uid))
BEGIN
    PRINT 'Updating TSQL Query collector type'
    EXEC sp_syscollector_update_collector_type 
            @collector_type_uid = @collector_type_uid,
            @name = @name,
            @parameter_schema = @parameter_schema,
            @parameter_formatter = @parameter_formatter,
            @collection_package_id = @collection_package_id,
            @upload_package_id = @upload_package_id
END
ELSE
BEGIN
    PRINT 'Creating T-SQL Query collector type'
    EXEC sp_syscollector_create_collector_type
            @collector_type_uid = @collector_type_uid,
            @name = @name,
            @parameter_schema = @parameter_schema,
            @parameter_formatter = @parameter_formatter,
            @collection_package_id = @collection_package_id,
            @upload_package_id = @upload_package_id
END

-- mark the collector type as system
UPDATE syscollector_collector_types 
SET is_system = 1
WHERE collector_type_uid = @collector_type_uid
GO

---------------------------------------------------------------
-- Database objects for TSQL query collector type 
---------------------------------------------------------------

IF (OBJECT_ID(N'[dbo].[syscollector_tsql_query_collector]', 'U') IS NULL)
BEGIN
    PRINT 'Creating table [dbo].[syscollector_tsql_query_collector]...'
    CREATE TABLE [dbo].[syscollector_tsql_query_collector] (
        collection_set_uid            uniqueidentifier NOT NULL,
        collection_set_id            int NOT NULL,
        collection_item_id            int NOT NULL,
        collection_package_id        uniqueidentifier NOT NULL,
        upload_package_id            uniqueidentifier NOT NULL,
        )
    ALTER TABLE syscollector_tsql_query_collector
        ADD CONSTRAINT [FK_syscollector_tsql_query_collector_syscollector_collection_items_internal] FOREIGN KEY(collection_set_id, collection_item_id)
        REFERENCES syscollector_collection_items_internal (collection_set_id, collection_item_id) ON DELETE CASCADE
END
GO

IF (OBJECT_ID('dbo.syscollector_collection_item_parameter_update_trigger', 'TR') IS NOT NULL)
BEGIN
    PRINT 'Dropping trigger [dbo].[syscollector_collection_item_parameter_update_trigger] on [dbo].[syscollector_collection_items_internal]'
    DROP TRIGGER [dbo].[syscollector_collection_item_parameter_update_trigger]
END
GO

PRINT 'Creating trigger [dbo].[syscollector_collection_item_parameter_update_trigger] on [dbo].[syscollector_collection_items_internal]'
GO
CREATE TRIGGER [dbo].[syscollector_collection_item_parameter_update_trigger] on [dbo].[syscollector_collection_items_internal]
FOR UPDATE
AS
BEGIN
    DECLARE @collection_set_id int
    DECLARE @collection_item_id int

    -- remove the TSQL query collection item that was updated so packages will be regenerated 
    -- base on the new parameters
    IF (NOT UPDATE (parameters))
       RETURN

    -- clean up the SSIS packages that are left behind
    DECLARE inserted_cursor CURSOR LOCAL FOR
        SELECT collection_set_id, collection_item_id
        FROM inserted
    
    OPEN inserted_cursor
    FETCH inserted_cursor INTO @collection_set_id, @collection_item_id

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DELETE FROM dbo.syscollector_tsql_query_collector 
        WHERE collection_set_id = @collection_set_id
        AND collection_item_id = @collection_item_id

        FETCH inserted_cursor INTO @collection_set_id, @collection_item_id
    END

    CLOSE inserted_cursor
    DEALLOCATE inserted_cursor
END
GO

IF (OBJECT_ID('dbo.syscollector_tsql_query_collector_delete_trigger', 'TR') IS NOT NULL)
BEGIN
    PRINT 'Dropping trigger [dbo].[syscollector_tsql_query_collector_delete_trigger] on [dbo].[syscollector_tsql_query_collector]'
    DROP TRIGGER [dbo].[syscollector_tsql_query_collector_delete_trigger]
END
GO

PRINT 'Creating trigger [dbo].[syscollector_tsql_query_collector_delete_trigger] on [dbo].[syscollector_tsql_query_collector]'
GO
CREATE TRIGGER [dbo].[syscollector_tsql_query_collector_delete_trigger] on [dbo].[syscollector_tsql_query_collector]
FOR DELETE
AS
BEGIN
    -- remove the SSIS packages left behind when the collection item is deleted 
    DECLARE @collection_package_id uniqueidentifier
    DECLARE @collection_package_folderid uniqueidentifier
    DECLARE @collection_package_name sysname

    DECLARE @upload_package_id  uniqueidentifier
    DECLARE @upload_package_folderid  uniqueidentifier
    DECLARE @upload_package_name  sysname

    DECLARE deleted_cursor CURSOR LOCAL FOR
        SELECT collection_package_id, upload_package_id
        FROM deleted
    
    OPEN deleted_cursor
    FETCH deleted_cursor INTO @collection_package_id, @upload_package_id

    WHILE @@FETCH_STATUS = 0
    BEGIN
        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

        EXEC dbo.sp_ssis_deletepackage
            @name = @collection_package_name,
            @folderid = @collection_package_folderid

        EXEC dbo.sp_ssis_deletepackage
            @name = @upload_package_name,
            @folderid = @upload_package_folderid

        FETCH deleted_cursor INTO @collection_package_id, @upload_package_id
    END

    CLOSE deleted_cursor
    DEALLOCATE deleted_cursor
END
GO

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

PRINT 'Creating procedure [dbo].[sp_syscollector_create_tsql_query_collector]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_create_tsql_query_collector]
    @collection_set_uid            uniqueidentifier,
    @collection_item_id            int,
    @collection_package_id        uniqueidentifier,
    @upload_package_id            uniqueidentifier
AS
BEGIN
    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_operator'), 0) = 1) AND 
        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_operator'' or ''dc_proxy')
        RETURN(1) -- Failure
    END

    DECLARE @errMsg VARCHAR(256)
    DECLARE @collection_set_id int
    SELECT @collection_set_id = s.collection_set_id
    FROM dbo.syscollector_collection_items i, dbo.syscollector_collection_sets s
    WHERE i.collection_item_id = @collection_item_id
    AND i.collector_type_uid = '302E93D1-3424-4be7-AA8E-84813ECF2419'
    AND s.collection_set_uid = @collection_set_uid

    -- Verify that the collection item exists of the correct type
    IF (@collection_set_id IS NULL)
    BEGIN        
        SELECT @errMsg = CONVERT(VARCHAR(36), @collection_set_uid) + ', ' + CONVERT(VARCHAR(36), @collection_item_id)
        RAISERROR(14262, -1, -1, '@collection_set_uid, @collection_item_id', @errMsg)
        RETURN(1)
    END

    -- Get the names and folder ids for the generated packages
    DECLARE @upload_package_name sysname
    DECLARE @upload_package_folder_id uniqueidentifier
    SELECT @upload_package_name = name, @upload_package_folder_id = folderid
    FROM sysssispackages
    WHERE id = @upload_package_id
    
    IF (@upload_package_name IS NULL) 
    BEGIN
        SELECT @errMsg = @upload_package_name + ', ' + CONVERT(VARCHAR(36), @upload_package_folder_id)
        RAISERROR(14262, -1, -1, '@upload_package_name, @upload_package_folder_id', @errMsg)
        RETURN(1)
    END

    DECLARE @collection_package_name sysname
    DECLARE @collection_package_folder_id uniqueidentifier
    SELECT @collection_package_name = name, @collection_package_folder_id = folderid
    FROM sysssispackages
    WHERE id = @collection_package_id
    
    IF (@collection_package_name IS NULL) 
    BEGIN
        SELECT @errMsg = @collection_package_name + ', ' + CONVERT(VARCHAR(36), @collection_package_folder_id)
        RAISERROR(14262, -1, -1, '@collection_package_name, @collection_package_folder_id', @errMsg)
        RETURN(1)
    END

    -- we need to allow dc_admin to delete these packages along with the collection set when 
    -- the set is deleted
    EXEC sp_ssis_setpackageroles @name = @upload_package_name, @folderid = @upload_package_folder_id, @readrole = NULL, @writerole = N'dc_admin'
    EXEC sp_ssis_setpackageroles @name = @collection_package_name, @folderid = @collection_package_folder_id, @readrole = NULL, @writerole = N'dc_admin'

    INSERT INTO [dbo].[syscollector_tsql_query_collector]
    (
        collection_set_uid,
        collection_set_id, 
        collection_item_id,
        collection_package_id,
        upload_package_id
    )
    VALUES
    (
        @collection_set_uid,
        @collection_set_id,
        @collection_item_id,
        @collection_package_id,
        @upload_package_id
    )
END
GO

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

-- get and return the collection and upload package IDs
-- if they do not exist, return empty IDs
PRINT 'Creating procedure [dbo].[sp_syscollector_get_tsql_query_collector_package_ids]...'
GO
CREATE PROCEDURE [dbo].[sp_syscollector_get_tsql_query_collector_package_ids]
    @collection_set_uid            uniqueidentifier,
    @collection_item_id            int,
    @collection_package_id        uniqueidentifier OUTPUT,
    @upload_package_id            uniqueidentifier OUTPUT,
    @collection_package_name    sysname OUTPUT,
    @upload_package_name        sysname OUTPUT    
AS
BEGIN
    -- Security check (role membership)
    IF (NOT (ISNULL(IS_MEMBER(N'dc_operator'), 0) = 1) AND 
        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_operator'' or ''dc_proxy')
        RETURN(1) -- Failure
    END

    SELECT @collection_package_id = collection_package_id,
        @upload_package_id = upload_package_id
    FROM dbo.syscollector_tsql_query_collector
    WHERE @collection_item_id = collection_item_id 
      AND @collection_set_uid = collection_set_uid

    IF(@collection_package_id IS NOT NULL AND @upload_package_id IS NOT NULL)
    BEGIN
        SELECT @collection_package_name = name
        FROM dbo.sysssispackages
        WHERE @collection_package_id = id

        SELECT @upload_package_name = name
        FROM dbo.sysssispackages
        WHERE @upload_package_id = id
    END
END
GO

-- SQLTrace collector type
DECLARE @collector_type_uid uniqueidentifier
DECLARE @name sysname
DECLARE @parameter_schema xml
DECLARE @parameter_formatter xml
DECLARE @collection_package_id uniqueidentifier
DECLARE @upload_package_id uniqueidentifier

SET @collector_type_uid = '0E218CF8-ECB5-417B-B533-D851C0251271'
SET @name = 'Generic SQL Trace Collector Type'
SET @parameter_schema = '<?xml version="1.0" encoding="utf-8"?>
                        <xs:schema targetNamespace="DataCollectorType" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                         <xs:element name="SqlTraceCollector">
                          <xs:complexType>
                           <xs:sequence>
                            <xs:element name="Events">
                             <xs:complexType>
                              <xs:sequence>
                               <xs:element minOccurs="0" maxOccurs="unbounded" name="EventType">
                                <xs:complexType>
                                 <xs:sequence>
                                  <xs:element maxOccurs="unbounded" name="Event">
                                   <xs:complexType>
                                    <xs:attribute name="id" type="xs:unsignedByte" use="required" />
                                    <xs:attribute name="name" type="xs:string" use="required" />
                                    <xs:attribute name="columnslist" type="xs:string" use="optional" />
                                   </xs:complexType>
                                  </xs:element>
                                 </xs:sequence>
                                 <xs:attribute name="id" type="xs:unsignedByte" use="optional" />
                                 <xs:attribute name="name" type="xs:string" use="required" />
                                </xs:complexType>
                               </xs:element>
                              </xs:sequence>
                             </xs:complexType>
                            </xs:element>
                            <xs:element name="Filters">
                             <xs:complexType>
                              <xs:sequence>
                               <xs:element name="Filter" minOccurs="0" maxOccurs="unbounded">
                                <xs:complexType>
                                 <xs:attribute name="columnid" type="xs:unsignedByte" use="required" />
                                 <xs:attribute name="columnname" type="xs:string" use="required" />
                                 <xs:attribute name="logical_operator" type="xs:string" use="required" />
                                 <xs:attribute name="comparison_operator" type="xs:string" use="required" />
                                 <xs:attribute name="value" type="xs:string" use="required" />
                                </xs:complexType>
                               </xs:element>
                              </xs:sequence>
                             </xs:complexType>
                            </xs:element>
                           </xs:sequence>
                           <xs:attribute name="use_default" type="xs:boolean" />
                          </xs:complexType>
                         </xs:element>
                        </xs:schema>'
SET @parameter_formatter = 
        N'<xsl:stylesheet 
            xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
            version="1.0"
            xmlns:z="#RowsetSchema"
            >
        <xsl:template match="/SqlTraceCollector">
            <HTML>
            <HEAD>
            <TITLE></TITLE>
            </HEAD>
            <BODY>
            <xsl:apply-templates select="Events"/>
            <HR/>
            <xsl:apply-templates select="Filters"/>
            </BODY>
            </HTML>
        </xsl:template>
        <xsl:template match="Events">
            <xsl:apply-templates select="EventType"/>
            <BR/>
        </xsl:template>
        <xsl:template match="EventType">
            <I> <PRE> ID = <xsl:value-of select="@id"/> - <xsl:value-of select="@name"/> </PRE> </I> 
            <UL>
            <xsl:apply-templates select="Event"/>
            </UL>
        </xsl:template>
        <xsl:template match="Event">
            <LI>
            <PRE> ID = <xsl:value-of select="@id"/> - <xsl:value-of select="@name"/> </PRE>
            </LI>
        </xsl:template>
        <xsl:template match="Filters">
            <UL>
            <xsl:apply-templates select="Filter" />
            </UL>
        </xsl:template>
        <xsl:template match="Filter">
            <PRE> <xsl:value-of select="@logical_operator"/> - <xsl:value-of select="@columnname"/> - <xsl:value-of select="@comparison_operator"/> - <xsl:value-of select="@value"/> </PRE>
        </xsl:template>
        </xsl:stylesheet>'
SET @collection_package_id = '0E149FC9-1046-4DE6-98BF-4B22ED6F6C42'
SET @upload_package_id = 'F389A8E6-5A17-4056-ABFD-C8B823F2092E' 

IF(EXISTS (SELECT * FROM dbo.syscollector_collector_types 
            WHERE collector_type_uid = @collector_type_uid))
BEGIN
    PRINT 'Updating SQL Trace collector type'
    EXEC sp_syscollector_update_collector_type 
            @collector_type_uid = @collector_type_uid,
            @name = @name,
            @parameter_schema = @parameter_schema,
            @parameter_formatter = @parameter_formatter,
            @collection_package_id = @collection_package_id,
            @upload_package_id = @upload_package_id
END
ELSE
BEGIN
    PRINT 'Creating SQL Trace collector type'
    EXEC sp_syscollector_create_collector_type
            @collector_type_uid = @collector_type_uid,
            @name = @name,
            @parameter_schema = @parameter_schema,
            @parameter_formatter = @parameter_formatter,
            @collection_package_id = @collection_package_id,
            @upload_package_id = @upload_package_id
END

-- mark the collector type as system
UPDATE syscollector_collector_types 
SET is_system = 1
WHERE collector_type_uid = @collector_type_uid
GO

-- Query Activity Collector Type
DECLARE @collector_type_uid uniqueidentifier
DECLARE @name sysname
DECLARE @collection_package_id uniqueidentifier
DECLARE @upload_package_id uniqueidentifier

SET @collector_type_uid = '14AF3C12-38E6-4155-BD29-F33E7966BA23'
SET @name = 'Query Activity Collector Type'
SET @collection_package_id = '0B68FC9D-23DC-48F3-A937-90A0A8943D0E'
SET @upload_package_id = '833DB628-8E19-47A3-92C5-FB1779B52E76'

IF(EXISTS (SELECT * FROM dbo.syscollector_collector_types 
            WHERE collector_type_uid = @collector_type_uid))
BEGIN
    PRINT 'Updating Query Activity Collector Type'
    EXEC sp_syscollector_update_collector_type
        @collector_type_uid = @collector_type_uid,
        @name = @name,
        @parameter_schema = NULL,
        @parameter_formatter = NULL,
        @collection_package_id = @collection_package_id,
        @upload_package_id = @upload_package_id
END
ELSE
BEGIN
    PRINT 'Creating Query Activity Collector Type'
    EXEC sp_syscollector_create_collector_type
        @collector_type_uid = @collector_type_uid,
        @name = @name,
        @parameter_schema = NULL,
        @parameter_formatter = NULL,
        @collection_package_id = @collection_package_id,
        @upload_package_id = @upload_package_id
END

-- mark the collector type as system
UPDATE syscollector_collector_types
SET is_system = 1
WHERE collector_type_uid = @collector_type_uid
GO

---------------------------------------------------------------
-- Out-of-the-box collector objects - Generic schedules for system collection sets
---------------------------------------------------------------
PRINT 'Creating data collector schedules'

DECLARE @schedule_name sysname
SET @schedule_name = N'CollectorSchedule_Every_5min'
IF  NOT EXISTS (SELECT name FROM msdb.dbo.sysschedules_localserver_view WHERE name = @schedule_name)
BEGIN
    EXEC dbo.sp_add_schedule
        @schedule_name = @schedule_name,    -- Schedule name
        @freq_type = 4,                        -- Daily
        @freq_interval = 1,                    -- Recurs every 1 day
        @freq_subday_type = 0x4,            -- Frequency type is "minutes"
        @freq_subday_interval = 5            -- Occurs every 5 minutes
END

SET @schedule_name = N'CollectorSchedule_Every_10min'
IF  NOT EXISTS (SELECT name FROM msdb.dbo.sysschedules_localserver_view WHERE name = @schedule_name)
BEGIN
    EXEC dbo.sp_add_schedule
        @schedule_name = @schedule_name,    -- Schedule name
        @freq_type = 4,                        -- Daily
        @freq_interval = 1,                    -- Recurs every 1 day
        @freq_subday_type = 0x4,            -- Frequency type is "minutes"
        @freq_subday_interval = 10            -- Occurs every 10 minutes
END

SET @schedule_name = N'CollectorSchedule_Every_15min'
IF  NOT EXISTS (SELECT name FROM msdb.dbo.sysschedules_localserver_view WHERE name = @schedule_name)
BEGIN
    EXEC dbo.sp_add_schedule
        @schedule_name = @schedule_name,    -- Schedule name
        @freq_type = 4,                        -- Daily
        @freq_interval = 1,                    -- Recurs every 1 day
        @freq_subday_type = 0x4,            -- Frequency type is "minutes"
        @freq_subday_interval = 15            -- Occurs every 15 minutes
END

SET @schedule_name = N'CollectorSchedule_Every_30min'
IF  NOT EXISTS (SELECT name FROM msdb.dbo.sysschedules_localserver_view WHERE name = @schedule_name)
BEGIN
    EXEC dbo.sp_add_schedule
        @schedule_name = @schedule_name,    -- Schedule name
        @freq_type = 4,                        -- Daily
        @freq_interval = 1,                    -- Recurs every 1 day
        @freq_subday_type = 0x4,            -- Frequency type is "minutes"
        @freq_subday_interval = 30            -- Occurs every 30 minutes
END

SET @schedule_name = N'CollectorSchedule_Every_60min'
IF  NOT EXISTS (SELECT name FROM msdb.dbo.sysschedules_localserver_view WHERE name = @schedule_name)
BEGIN
    EXEC dbo.sp_add_schedule
        @schedule_name = @schedule_name,    -- Schedule name
        @freq_type = 4,                        -- Daily
        @freq_interval = 1,                    -- Recurs every 1 day
        @freq_subday_type = 0x4,            -- Frequency type is "minutes"
        @freq_subday_interval = 60            -- Occurs every 60 minutes
END

SET @schedule_name = N'CollectorSchedule_Every_6h'
IF  NOT EXISTS (SELECT name FROM msdb.dbo.sysschedules_localserver_view WHERE name = @schedule_name)
BEGIN
    EXEC dbo.sp_add_schedule
        @schedule_name = @schedule_name,    -- Schedule name
        @freq_type = 4,                        -- Daily
        @freq_interval = 1,                    -- Recurs every 1 day
        @freq_subday_type = 0x8,            -- Frequency type is "hours"
        @freq_subday_interval = 6            -- Occurs every 6 hours
END
GO

---------------------------------------------------------------
-- Out-of-the-box system Collection Sets 
---------------------------------------------------------------
PRINT 'Creating system Collection Sets...'

------------------------------------------------
-- System collection set: Disk Usage
------------------------------------------------
DECLARE @collection_set_name NVARCHAR(128);
DECLARE @description NVARCHAR(4000);
DECLARE @collection_set_id int;
DECLARE @collection_set_uid uniqueidentifier;
DECLARE @collection_mode smallint;
DECLARE @schedule_name sysname;
DECLARE @days_until_expiration smallint;
DECLARE @name_id int;
DECLARE @description_id int;

-- The GUID below identifies this collection set and is used to locate the data collected by this collection set. 
-- The name and description retrived via FORMATMESSAGE will not be localized for the initial mkmastr build of msdb, 
-- but that is OK; the public interface to collection set detils (syscollector_collection_sets) is a view that will 
-- dynamically pull the correct localized strings via its own FORMATMESSAGE calls.  We just need a unique string to 
-- store in the table.
SET @name_id = 14701;
SET @description_id = 14700;
SET @collection_set_uid = N'7B191952-8ECF-4E12-AEB2-EF646EF79FEF';
SET @collection_mode = 1; -- Non-cached
SET @schedule_name = N'CollectorSchedule_Every_6h';
SET @days_until_expiration = 730;

SET @description = FORMATMESSAGE(@description_id);
SET @collection_set_name = ISNULL (FORMATMESSAGE(@name_id), 'Disk Usage');

IF EXISTS (SELECT * FROM dbo.syscollector_collection_sets_internal WHERE collection_set_uid = @collection_set_uid)
BEGIN
    RAISERROR ('Updating system Collection Set "%s"...', 0, 1, @collection_set_name) WITH NOWAIT;
    -- We are updating an existing collection set -- get its ID 
    SELECT @collection_set_id = collection_set_id
    FROM syscollector_collection_sets 
    WHERE collection_set_uid = @collection_set_uid;
    
    -- Temporarily clear the is_system flag so that we can modify the collection set definition
    UPDATE syscollector_collection_sets
    SET is_system = 0
    WHERE collection_set_id = @collection_set_id
    
    -- Don't override the current expiration period or schedule settings, since the user may have customized these
    EXEC dbo.sp_syscollector_update_collection_set
        @collection_set_id = @collection_set_id, 
        @new_name = @collection_set_name, 
        @schedule_name = @schedule_name, -- avoid mismatch in uid if schedule was deleted and recreated
        @collection_mode = @collection_mode, 
        @logging_level = 0,
        @description = @description;
END
ELSE
BEGIN    
    RAISERROR ('Creating system Collection Set "%s"...', 0, 1, @collection_set_name) WITH NOWAIT;
    EXEC dbo.sp_syscollector_create_collection_set
        @collection_set_uid = @collection_set_uid,
        @name = @collection_set_name, 
        @schedule_name = @schedule_name, 
        @collection_mode = @collection_mode, 
        @days_until_expiration = @days_until_expiration, 
        @description = @description,
        @logging_level = 0, 
        @collection_set_id = @collection_set_id OUTPUT;
END

-- for localization of collection set name and description
UPDATE syscollector_collection_sets_internal
SET name_id = @name_id, description_id = @description_id
WHERE collection_set_uid = @collection_set_uid;

-- Add collection items
DECLARE @collection_item_name NVARCHAR(128);
DECLARE @collection_item_old_name NVARCHAR(128);
DECLARE @collection_item_id int;
DECLARE @frequency int;

-- Item 1: disk_usage DMV query
DECLARE @parameters xml;
SELECT @parameters = convert(xml, N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
<Query>
<Value>
DECLARE @dbsize bigint 
DECLARE @logsize bigint 
DECLARE @ftsize bigint 
DECLARE @reservedpages bigint 
DECLARE @pages bigint 
DECLARE @usedpages bigint

SELECT @dbsize = SUM(convert(bigint,case when type = 0 then size else 0 end)) 
      ,@logsize = SUM(convert(bigint,case when type = 1 then size else 0 end)) 
      ,@ftsize = SUM(convert(bigint,case when type = 4 then size else 0 end)) 
FROM sys.database_files

SELECT @reservedpages = SUM(a.total_pages) 
       ,@usedpages = SUM(a.used_pages) 
       ,@pages = SUM(CASE 
                        WHEN it.internal_type IN (202,204) THEN 0 
                        WHEN a.type != 1 THEN a.used_pages 
                        WHEN p.index_id &lt; 2 THEN a.data_pages 
                        ELSE 0 
                     END) 
FROM sys.partitions p  
JOIN sys.allocation_units a ON p.partition_id = a.container_id 
LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id 

SELECT 
        @dbsize as ''dbsize'',
        @logsize as ''logsize'',
        @ftsize as ''ftsize'',
        @reservedpages as ''reservedpages'',
        @usedpages as ''usedpages'',
        @pages as ''pages''
</Value>
<OutputTable>disk_usage</OutputTable>
</Query>
<Databases UseSystemDatabases="true" UseUserDatabases="true" />
</ns:TSQLQueryCollector>');

-- The name and description retrived via FORMATMESSAGE will not be localized for the initial mkmastr build of msdb, 
-- but that is OK; the public interface to collection item detils (syscollector_collection_items) is a view that will 
-- dynamically pull the correct localized strings via its own FORMATMESSAGE calls.  We just need a unique string to 
-- store in the table.
SET @name_id = 14702;
SET @collection_item_name = FORMATMESSAGE(@name_id);
SET @collection_item_old_name = 'Disk Usage - Data Files';
SET @frequency = 60;  -- Ignored (this collection set uses non-cached collection mode)

IF ISNULL (@collection_item_name, '') = '' 
BEGIN
    SET @collection_item_name = @collection_item_old_name;
END;

SET @collection_item_id = NULL;
SELECT @collection_item_id = collection_item_id
FROM syscollector_collection_items_internal 
WHERE collection_set_id = @collection_set_id 
    AND (name = @collection_item_name OR name = @collection_item_old_name);

IF (@collection_item_id IS NOT NULL)
BEGIN
    RAISERROR ('Updating Collection Item "%s"...', 0, 1, @collection_item_name);
    EXEC dbo.sp_syscollector_update_collection_item 
        @collection_item_id = @collection_item_id, 
        @new_name = @collection_item_name, 
        @frequency = @frequency, 
        @parameters = @parameters;
END
ELSE
BEGIN
    RAISERROR ('Creating Collection Item "%s"...', 0, 1, @collection_item_name);
    EXEC dbo.sp_syscollector_create_collection_item
        @collection_set_id = @collection_set_id,
        @collector_type_uid = N'302E93D1-3424-4BE7-AA8E-84813ECF2419',
        @name = @collection_item_name,
        @parameters = @parameters,
        @frequency = @frequency, 
        @collection_item_id = @collection_item_id output;
END;

-- for localization of collection item name
UPDATE syscollector_collection_items_internal
SET name_id = @name_id
WHERE collection_item_id = @collection_item_id;

-- Item 2: log_usage DMV query
SELECT @parameters = convert(xml, N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
<Query>
<Value>
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @tran_log_space_usage table( 
        database_name sysname
,       log_size_mb float
,       log_space_used float
,       status int
); 
INSERT INTO @tran_log_space_usage 
EXEC(''DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS'');
 
SELECT 
    database_name,
    log_size_mb,
    log_space_used,
    status    
FROM @tran_log_space_usage
</Value>
<OutputTable>log_usage</OutputTable>
</Query>
</ns:TSQLQueryCollector>');

-- The name and description retrived via FORMATMESSAGE will not be localized for the initial mkmastr build of msdb, 
-- but that is OK; the public interface to collection item detils (syscollector_collection_items) is a view that will 
-- dynamically pull the correct localized strings via its own FORMATMESSAGE calls.  We just need a unique string to 
-- store in the table.
SET @name_id = 14703;
SET @collection_item_name = FORMATMESSAGE(@name_id);
SET @collection_item_old_name = 'Disk Usage - Log Files';
SET @frequency = 60;  -- Ignored (this collection set uses non-cached collection mode)

IF ISNULL (@collection_item_name, '') = '' 
BEGIN
    SET @collection_item_name = @collection_item_old_name;
END;

SET @collection_item_id = NULL;
SELECT @collection_item_id = collection_item_id
FROM syscollector_collection_items_internal 
WHERE collection_set_id = @collection_set_id 
    AND (name = @collection_item_name OR name = @collection_item_old_name);

IF (@collection_item_id IS NOT NULL)
BEGIN
    RAISERROR ('Updating Collection Item "%s"...', 0, 1, @collection_item_name);
    EXEC dbo.sp_syscollector_update_collection_item 
        @collection_item_id = @collection_item_id, 
        @new_name = @collection_item_name, 
        @frequency = @frequency, 
        @parameters = @parameters;
END
ELSE
BEGIN
    RAISERROR ('Creating Collection Item "%s"...', 0, 1, @collection_item_name);
    EXEC dbo.sp_syscollector_create_collection_item
        @collection_set_id = @collection_set_id,
        @collector_type_uid = N'302E93D1-3424-4BE7-AA8E-84813ECF2419',
        @name = @collection_item_name,
        @parameters = @parameters,
        @frequency = @frequency, 
        @collection_item_id = @collection_item_id output;
END;

-- for localization of collection item name
UPDATE syscollector_collection_items_internal
SET name_id = @name_id
WHERE collection_item_id = @collection_item_id;

-- Turn the is_system flag on so users can't change the definition of this collection set 
UPDATE syscollector_collection_sets
SET is_system = 1
WHERE collection_set_id = @collection_set_id
GO


------------------------------------------------
-- System collection set: Server Activity
------------------------------------------------
DECLARE @collection_set_name NVARCHAR(128);
DECLARE @description NVARCHAR(4000);
DECLARE @collection_set_id int;
DECLARE @collection_set_uid uniqueidentifier;
DECLARE @collection_mode smallint;
DECLARE @schedule_name sysname;
DECLARE @days_until_expiration smallint;
DECLARE @name_id int;
DECLARE @description_id int;

-- The GUID below identifies this collection set and is used to locate the data collected by this collection set. 
-- The name and description retrived via FORMATMESSAGE will not be localized for the initial mkmastr build of msdb, 
-- but that is OK; the public interface to collection set detils (syscollector_collection_sets) is a view that will 
-- dynamically pull the correct localized strings via its own FORMATMESSAGE calls.  We just need a unique string to 
-- store in the table.
SET @name_id = 14705;
SET @description_id = 14704;
SET @collection_set_uid = N'49268954-4FD4-4EB6-AA04-CD59D9BB5714';
SET @collection_mode = 0; -- Cached
SET @schedule_name = N'CollectorSchedule_Every_15min';
SET @days_until_expiration = 14;

SET @description = FORMATMESSAGE(@description_id);
SET @collection_set_name = ISNULL (FORMATMESSAGE(@name_id), 'Server Activity');

IF EXISTS (SELECT * FROM dbo.syscollector_collection_sets_internal WHERE collection_set_uid = @collection_set_uid)
BEGIN
    RAISERROR ('Updating system Collection Set "%s"...', 0, 1, @collection_set_name) WITH NOWAIT;
    -- We are updating an existing collection set -- get its ID 
    SELECT @collection_set_id = collection_set_id
    FROM syscollector_collection_sets 
    WHERE collection_set_uid = @collection_set_uid;
    
    -- Temporarily clear the is_system flag so that we can modify the collection set definition
    UPDATE syscollector_collection_sets
    SET is_system = 0
    WHERE collection_set_id = @collection_set_id
    
    -- Don't override the current expiration period or schedule settings, since the user may have customized these
    EXEC dbo.sp_syscollector_update_collection_set
        @collection_set_id = @collection_set_id, 
        @new_name = @collection_set_name, 
        @schedule_name = @schedule_name, -- avoid mismatch in uid if schedule was deleted and recreated
        @collection_mode = @collection_mode, 
        @logging_level = 0,
        @description = @description;
END
ELSE
BEGIN    
    RAISERROR ('Creating system Collection Set "%s"...', 0, 1, @collection_set_name) WITH NOWAIT;
    EXEC dbo.sp_syscollector_create_collection_set
        @collection_set_uid = @collection_set_uid,
        @name = @collection_set_name, 
        @schedule_name = @schedule_name, 
        @collection_mode = @collection_mode, 
        @days_until_expiration = @days_until_expiration, 
        @description = @description, 
        @logging_level = 0,
        @collection_set_id = @collection_set_id OUTPUT;
END

-- for localization of collection set name and description
UPDATE syscollector_collection_sets_internal
SET name_id = @name_id, description_id = @description_id
WHERE collection_set_uid = @collection_set_uid;

-- Add collection items
DECLARE @collection_item_name NVARCHAR(128);
DECLARE @collection_item_old_name NVARCHAR(128);
DECLARE @collection_item_id int;
DECLARE @frequency int;
DECLARE @parameters xml;

-- Item 1 - DMV SNAPSHOTS
SELECT @parameters = convert(xml, N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
<Query>
<Value>
SET NOCOUNT ON
SELECT 
    LEFT (wait_type, 45) AS wait_type, 
    SUM (waiting_tasks_count) AS waiting_tasks_count, 
    SUM (wait_time_ms) AS wait_time_ms, 
    SUM (signal_wait_time_ms) AS signal_wait_time_ms
FROM 
(
    SELECT 
        LEFT (wait_type, 45) AS wait_type, 
    waiting_tasks_count, 
    wait_time_ms,  
    signal_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE waiting_tasks_count &gt; 0 OR wait_time_ms &gt; 0 OR signal_wait_time_ms &gt; 0
    UNION ALL 
    SELECT 
        LEFT (wait_type, 45) AS wait_type, 
        1 AS waiting_tasks_count, 
        wait_duration_ms AS wait_time_ms, 
        0 AS signal_wait_time_ms
    FROM sys.dm_os_waiting_tasks
    WHERE wait_duration_ms &gt; 60000
) AS merged_wait_stats
GROUP BY wait_type
</Value>
<OutputTable>os_wait_stats</OutputTable>
</Query>
<Query>
<Value>
SET NOCOUNT ON
SELECT 
  LEFT(latch_class,45) as latch_class,
  waiting_requests_count,
  wait_time_ms
FROM sys.dm_os_latch_stats 
WHERE waiting_requests_count &gt; 0 OR wait_time_ms &gt; 0
</Value>
<OutputTable>os_latch_stats</OutputTable>
</Query>
<Query>
<Value>
SET NOCOUNT ON
SELECT 
    pm.physical_memory_in_use_kb            AS sql_physical_memory_in_use_kb, 
    pm.large_page_allocations_kb            AS sql_large_page_allocations_kb, 
    pm.locked_page_allocations_kb           AS sql_locked_page_allocations_kb, 
    pm.total_virtual_address_space_kb       AS sql_total_virtual_address_space_kb, 
    pm.virtual_address_space_reserved_kb    AS sql_virtual_address_space_reserved_kb, 
    pm.virtual_address_space_committed_kb   AS sql_virtual_address_space_committed_kb, 
    pm.virtual_address_space_available_kb   AS sql_virtual_address_space_available_kb, 
    pm.page_fault_count                     AS sql_page_fault_count, 
    pm.memory_utilization_percentage        AS sql_memory_utilization_percentage, 
    pm.available_commit_limit_kb            AS sql_available_commit_limit_kb, 
    pm.process_physical_memory_low          AS sql_process_physical_memory_low, 
    pm.process_virtual_memory_low           AS sql_process_virtual_memory_low, 
    
    sm.total_physical_memory_kb             AS system_total_physical_memory_kb, 
    sm.available_physical_memory_kb         AS system_available_physical_memory_kb, 
    sm.total_page_file_kb                   AS system_total_page_file_kb, 
    sm.available_page_file_kb               AS system_available_page_file_kb, 
    sm.system_cache_kb                      AS system_cache_kb, 
    sm.kernel_paged_pool_kb                 AS system_kernel_paged_pool_kb, 
    sm.kernel_nonpaged_pool_kb              AS system_kernel_nonpaged_pool_kb, 
    sm.system_high_memory_signal_state      AS system_high_memory_signal_state, 
    sm.system_low_memory_signal_state       AS system_low_memory_signal_state, 
    
    si.bpool_commit_target                  AS bpool_commit_target, 
    si.bpool_committed                      AS bpool_committed, 
    si.bpool_visible                        AS bpool_visible
FROM sys.dm_os_process_memory AS pm
CROSS JOIN sys.dm_os_sys_memory AS sm   -- single-row DMV
CROSS JOIN sys.dm_os_sys_info AS si;    -- single-row DMV
</Value>
<OutputTable>sql_process_and_system_memory</OutputTable>
</Query>
<Query>
<Value>
SET NOCOUNT ON
SELECT 
    memory_node_id, 
    virtual_address_space_reserved_kb, 
    virtual_address_space_committed_kb, 
    locked_page_allocations_kb, 
    single_pages_kb, 
    multi_pages_kb, 
    shared_memory_reserved_kb, 
    shared_memory_committed_kb
FROM sys.dm_os_memory_nodes
</Value>
<OutputTable>os_memory_nodes</OutputTable>
</Query>
<Query>
<Value>
SET NOCOUNT ON
SELECT 
    type,
    memory_node_id as memory_node_id,
    SUM(single_pages_kb) as single_pages_kb,
    SUM(multi_pages_kb) as multi_pages_kb,
    SUM(virtual_memory_reserved_kb) as virtual_memory_reserved_kb,
    SUM(virtual_memory_committed_kb) as virtual_memory_committed_kb,
    SUM(awe_allocated_kb) as awe_allocated_kb,
    SUM(shared_memory_reserved_kb) as shared_memory_reserved_kb,
    SUM(shared_memory_committed_kb) as shared_memory_committed_kb
FROM sys.dm_os_memory_clerks
GROUP BY type, memory_node_id</Value>
<OutputTable>os_memory_clerks</OutputTable>
</Query>
<Query>
<Value>
SET NOCOUNT ON
SELECT 
    [parent_node_id],
    [scheduler_id],
    [cpu_id],
    [status],
    [is_idle],
    [preemptive_switches_count],
    [context_switches_count],
    [yield_count],
    [current_tasks_count],
    [runnable_tasks_count],
    [work_queue_count],
    [pending_disk_io_count]
FROM sys.dm_os_schedulers
WHERE scheduler_id &lt; 128
</Value>
<OutputTable>os_schedulers</OutputTable>
</Query>
<Query>
<Value>
SELECT 
    DB_NAME (f.database_id) AS database_name, f.database_id, f.name AS logical_file_name, f.[file_id], f.type_desc, 
    CAST (CASE 
        -- Handle UNC paths (e.g. ''\\fileserver\readonlydbs\dept_dw.ndf'' --&gt; ''\\fileserver\readonlydbs'')
        WHEN LEFT (LTRIM (f.physical_name), 2) = ''\\'' 
            THEN LEFT (LTRIM (f.physical_name), CHARINDEX (''\'', LTRIM (f.physical_name), CHARINDEX (''\'', LTRIM (f.physical_name), 3) + 1) - 1)
        -- Handle local paths (e.g. ''C:\Program Files\...\master.mdf'' --&gt; ''C:'') 
        WHEN CHARINDEX (''\'', LTRIM(f.physical_name), 3) &gt; 0 
            THEN UPPER (LEFT (LTRIM (f.physical_name), CHARINDEX (''\'', LTRIM (f.physical_name), 3) - 1))
        ELSE f.physical_name
    END AS nvarchar(255)) AS logical_disk, 
    fs.num_of_reads, fs.num_of_bytes_read, fs.io_stall_read_ms, fs.num_of_writes, fs.num_of_bytes_written, 
    fs.io_stall_write_ms, fs.size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats (default, default) AS fs
INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.[file_id] = f.[file_id]
</Value>
<OutputTable>io_virtual_file_stats</OutputTable>
</Query>
</ns:TSQLQueryCollector>');

-- The name and description retrived via FORMATMESSAGE will not be localized for the initial mkmastr build of msdb, 
-- but that is OK; the public interface to collection item detils (syscollector_collection_items) is a view that will 
-- dynamically pull the correct localized strings via its own FORMATMESSAGE calls.  We just need a unique string to 
-- store in the table.
SET @name_id = 14706;
SET @collection_item_name = FORMATMESSAGE(@name_id);
SET @collection_item_old_name = 'Server Activity - DMV Snapshots';
SET @frequency = 60;  

IF ISNULL (@collection_item_name, '') = '' 
BEGIN
    SET @collection_item_name = @collection_item_old_name;
END;

SET @collection_item_id = NULL;
SELECT @collection_item_id = collection_item_id
FROM syscollector_collection_items_internal 
WHERE collection_set_id = @collection_set_id 
    AND (name = @collection_item_name OR name = @collection_item_old_name);

IF (@collection_item_id IS NOT NULL)
BEGIN
    RAISERROR ('Updating Collection Item "%s"...', 0, 1, @collection_item_name);
    EXEC dbo.sp_syscollector_update_collection_item 
        @collection_item_id = @collection_item_id, 
        @new_name = @collection_item_name, 
        @frequency = @frequency, 
        @parameters = @parameters;
END
ELSE
BEGIN
    RAISERROR ('Creating Collection Item "%s"...', 0, 1, @collection_item_name);
    EXEC dbo.sp_syscollector_create_collection_item
        @collection_set_id = @collection_set_id,
        @collector_type_uid = N'302E93D1-3424-4BE7-AA8E-84813ECF2419',
        @name = @collection_item_name,
        @parameters = @parameters,
        @frequency = @frequency, 
        @collection_item_id = @collection_item_id output;
END;

-- for localization of collection item name
UPDATE syscollector_collection_items_internal
SET name_id = @name_id
WHERE collection_item_id = @collection_item_id;

-- Item 2 - PERFORMANCE COUNTERS
SELECT @parameters = convert(xml, N'<ns:PerformanceCountersCollector xmlns:ns="DataCollectorType">
 <PerformanceCounters Objects="Memory" Counters="% Committed Bytes In Use" />
 <PerformanceCounters Objects="Memory" Counters="Available Bytes" />
 <PerformanceCounters Objects="Memory" Counters="Cache Bytes" />
 <PerformanceCounters Objects="Memory" Counters="Cache Faults/sec" />
 <PerformanceCounters Objects="Memory" Counters="Committed Bytes" />
 <PerformanceCounters Objects="Memory" Counters="Free &amp; Zero Page List Bytes" />
 <PerformanceCounters Objects="Memory" Counters="Modified Page List Bytes" />
 <PerformanceCounters Objects="Memory" Counters="Pages/sec" />
 <PerformanceCounters Objects="Memory" Counters="Page Reads/sec" />
 <PerformanceCounters Objects="Memory" Counters="Page Write/sec" />
 <PerformanceCounters Objects="Memory" Counters="Page Faults/sec" />
 <PerformanceCounters Objects="Memory" Counters="Pool Nonpaged Bytes" />
 <PerformanceCounters Objects="Memory" Counters="Pool Paged Bytes" />
 <PerformanceCounters Objects="Memory" Counters="Standby Cache Core Bytes" />
 <PerformanceCounters Objects="Memory" Counters="Standby Cache Normal Priority Bytes" />
 <PerformanceCounters Objects="Memory" Counters="Standby Cache Reserve Bytes" />
 <PerformanceCounters Objects="Memory" Counters="Pool Paged Bytes" />
 <PerformanceCounters Objects="Memory" Counters="Write Copies/sec" />
 <PerformanceCounters Objects="Process" Counters="*" Instances="_Total" />
 <PerformanceCounters Objects="Process" Counters="*" Instances="$(TARGETPROCESS)" />
 <PerformanceCounters Objects="Process" Counters="Thread Count" Instances="*" />
 <PerformanceCounters Objects="Process" Counters="% Processor Time" Instances="*" />
 <PerformanceCounters Objects="Process" Counters="IO Read Bytes/sec" Instances="*" />
 <PerformanceCounters Objects="Process" Counters="IO Write Bytes/sec" Instances="*" />
 <PerformanceCounters Objects="Process" Counters="Private Bytes" Instances="*" />
 <PerformanceCounters Objects="Process" Counters="Working Set" Instances="*" />
 <PerformanceCounters Objects="Processor" Counters="% Processor Time" Instances="*" />
 <PerformanceCounters Objects="Processor" Counters="% User Time" Instances="*" />
 <PerformanceCounters Objects="Processor" Counters="% Privileged Time" Instances="*" />
 <PerformanceCounters Objects="Server Work Queues" Counters="Queue Length" Instances="*" />
 <PerformanceCounters Objects="LogicalDisk" Counters="% Disk Time" Instances="*" />
 <PerformanceCounters Objects="LogicalDisk" Counters="Avg. Disk Queue Length" Instances="*" />
 <PerformanceCounters Objects="LogicalDisk" Counters="Avg. Disk Read Queue Length" Instances="*" />
 <PerformanceCounters Objects="LogicalDisk" Counters="Avg. Disk Write Queue Length" Instances="*" />
 <PerformanceCounters Objects="LogicalDisk" Counters="Avg. Disk sec/Read" Instances="*" />
 <PerformanceCounters Objects="LogicalDisk" Counters="Avg. Disk sec/Write" Instances="*" />
 <PerformanceCounters Objects="LogicalDisk" Counters="Avg. Disk sec/Transfer" Instances="*" />
 <PerformanceCounters Objects="LogicalDisk" Counters="Disk Reads/sec" Instances="*" />
 <PerformanceCounters Objects="LogicalDisk" Counters="Disk Bytes/sec" Instances="*" />
 <PerformanceCounters Objects="LogicalDisk" Counters="Disk Writes/sec" Instances="*" />
 <PerformanceCounters Objects="LogicalDisk" Counters="Split IO/sec" Instances="*" />
 <PerformanceCounters Objects="System" Counters="Processor Queue Length" />
 <PerformanceCounters Objects="System" Counters="File Read Operations/sec" />
 <PerformanceCounters Objects="System" Counters="File Write Operations/sec" />
 <PerformanceCounters Objects="System" Counters="File Control Operations/sec" />
 <PerformanceCounters Objects="System" Counters="File Read Bytes/sec" />
 <PerformanceCounters Objects="System" Counters="File Write Bytes/sec" />
 <PerformanceCounters Objects="System" Counters="File Control Bytes/sec" />
 <PerformanceCounters Objects="Network Interface" Counters="Bytes Total/sec" Instances="*" />
 <PerformanceCounters Objects="Network Interface" Counters="Output Queue Length" Instances="*" />
 <PerformanceCounters Objects="$(INSTANCE):Buffer Manager" Counters="Stolen pages"/>
 <PerformanceCounters Objects="$(INSTANCE):Buffer Manager" Counters="Page life expectancy"/>
 <PerformanceCounters Objects="$(INSTANCE):Memory Manager" Counters="Memory Grants Outstanding"/>
 <PerformanceCounters Objects="$(INSTANCE):Memory Manager" Counters="Memory Grants Pending"/>
 <PerformanceCounters Objects="$(INSTANCE):Databases" Counters="Transactions/sec"  Instances="_Total"/>
 <PerformanceCounters Objects="$(INSTANCE):Databases" Counters="Transactions/sec"  Instances="tempdb"/>
 <PerformanceCounters Objects="$(INSTANCE):Databases" Counters="Active Transactions"  Instances="*"/>
 <PerformanceCounters Objects="$(INSTANCE):General Statistics" Counters="Logins/sec"  />
 <PerformanceCounters Objects="$(INSTANCE):General Statistics" Counters="Logouts/sec"  />
 <PerformanceCounters Objects="$(INSTANCE):General Statistics" Counters="User Connections"  />
 <PerformanceCounters Objects="$(INSTANCE):General Statistics" Counters="Logical Connections"  />
 <PerformanceCounters Objects="$(INSTANCE):General Statistics" Counters="Transactions"  />
 <PerformanceCounters Objects="$(INSTANCE):General Statistics" Counters="Processes blocked"  />
 <PerformanceCounters Objects="$(INSTANCE):General Statistics" Counters="Active Temp Tables"  />
 <PerformanceCounters Objects="$(INSTANCE):SQL Statistics" Counters="Batch Requests/sec" />
 <PerformanceCounters Objects="$(INSTANCE):SQL Statistics" Counters="SQL Compilations/sec" />
 <PerformanceCounters Objects="$(INSTANCE):SQL Statistics" Counters="SQL Re-Compilations/sec" />
 <PerformanceCounters Objects="$(INSTANCE):SQL Statistics" Counters="SQL Attention rate" />
 <PerformanceCounters Objects="$(INSTANCE):SQL Statistics" Counters="Auto-Param Attempts/sec" />
 <PerformanceCounters Objects="$(INSTANCE):SQL Statistics" Counters="Failed Auto-Params/sec" />
 <PerformanceCounters Objects="$(INSTANCE):Plan Cache" Counters="Cache Hit Ratio" Instances="_Total" />
 <PerformanceCounters Objects="$(INSTANCE):Plan Cache" Counters="Cache Hit Ratio" Instances="Object Plans" />
 <PerformanceCounters Objects="$(INSTANCE):Plan Cache" Counters="Cache Hit Ratio" Instances="SQL Plans" />
 <PerformanceCounters Objects="$(INSTANCE):Plan Cache" Counters="Cache Hit Ratio" Instances="Temporary Tables &amp; Table Variables" />
 <PerformanceCounters Objects="$(INSTANCE):Transactions" Counters="Free Space in tempdb (KB)"/>
 <PerformanceCounters Objects="$(INSTANCE):Workload Group Stats" Counters="Active requests" Instances="*"/>
 <PerformanceCounters Objects="$(INSTANCE):Workload Group Stats" Counters="Blocked tasks" Instances="*"/>
 <PerformanceCounters Objects="$(INSTANCE):Workload Group Stats" Counters="CPU usage %" Instances="*"/>
</ns:PerformanceCountersCollector>');

-- The name and description retrived via FORMATMESSAGE will not be localized for the initial mkmastr build of msdb, 
-- but that is OK; the public interface to collection item detils (syscollector_collection_items) is a view that will 
-- dynamically pull the correct localized strings via its own FORMATMESSAGE calls.  We just need a unique string to 
-- store in the table.
SET @name_id = 14707;
SET @collection_item_name = FORMATMESSAGE(@name_id);
SET @collection_item_old_name = 'Server Activity - Performance Counters';
SET @frequency = 60;  

IF ISNULL (@collection_item_name, '') = '' 
BEGIN
    SET @collection_item_name = @collection_item_old_name;
END;

SET @collection_item_id = NULL;
SELECT @collection_item_id = collection_item_id
FROM syscollector_collection_items_internal 
WHERE collection_set_id = @collection_set_id 
    AND (name = @collection_item_name OR name = @collection_item_old_name);

IF (@collection_item_id IS NOT NULL)
BEGIN
    RAISERROR ('Updating Collection Item "%s"...', 0, 1, @collection_item_name);
    EXEC dbo.sp_syscollector_update_collection_item 
        @collection_item_id = @collection_item_id, 
        @new_name = @collection_item_name, 
        @frequency = @frequency, 
        @parameters = @parameters;
END
ELSE
BEGIN
    RAISERROR ('Creating Collection Item "%s"...', 0, 1, @collection_item_name);
    EXEC dbo.sp_syscollector_create_collection_item
        @collection_set_id = @collection_set_id,
        @collector_type_uid = N'294605DD-21DE-40B2-B20F-F3E170EA1EC3',
        @name = @collection_item_name,
        @parameters = @parameters,
        @frequency = @frequency, 
        @collection_item_id = @collection_item_id output;
END;

-- for localization of collection item name
UPDATE syscollector_collection_items_internal
SET name_id = @name_id
WHERE collection_item_id = @collection_item_id;

-- Turn the is_system flag on so users can't change the definition of this collection set 
UPDATE syscollector_collection_sets
SET is_system = 1
WHERE collection_set_id = @collection_set_id
GO


------------------------------------------------
-- System collection set: Query Statistics
------------------------------------------------
DECLARE @collection_set_name NVARCHAR(128);
DECLARE @description NVARCHAR(4000);
DECLARE @collection_set_id int;
DECLARE @collection_set_uid uniqueidentifier;
DECLARE @collection_mode smallint;
DECLARE @schedule_name sysname;
DECLARE @days_until_expiration smallint;
DECLARE @name_id int;
DECLARE @description_id int;

-- The GUID below identifies this collection set and is used to locate the data collected by this collection set. 
-- The name and description retrived via FORMATMESSAGE will not be localized for the initial mkmastr build of msdb, 
-- but that is OK; the public interface to collection set detils (syscollector_collection_sets) is a view that will 
-- dynamically pull the correct localized strings via its own FORMATMESSAGE calls.  We just need a unique string to 
-- store in the table.
SET @name_id = 14709;
SET @description_id = 14708;
SET @collection_set_uid = N'2DC02BD6-E230-4C05-8516-4E8C0EF21F95';
SET @collection_mode = 0; -- Cached
SET @schedule_name = N'CollectorSchedule_Every_15min';
SET @days_until_expiration = 14;

SET @description = FORMATMESSAGE(@description_id);
SET @collection_set_name = ISNULL (FORMATMESSAGE(@name_id), 'Query Statistics');

IF EXISTS (SELECT * FROM dbo.syscollector_collection_sets_internal WHERE collection_set_uid = @collection_set_uid)
BEGIN
    RAISERROR ('Updating system Collection Set "%s"...', 0, 1, @collection_set_name) WITH NOWAIT;
    -- We are updating an existing collection set -- get its ID 
    SELECT @collection_set_id = collection_set_id
    FROM syscollector_collection_sets 
    WHERE collection_set_uid = @collection_set_uid;
    
    -- Temporarily clear the is_system flag so that we can modify the collection set definition
    UPDATE syscollector_collection_sets
    SET is_system = 0
    WHERE collection_set_id = @collection_set_id
    
    -- Don't override the current expiration period or schedule settings, since the user may have customized these
    EXEC dbo.sp_syscollector_update_collection_set
        @collection_set_id = @collection_set_id, 
        @new_name = @collection_set_name, 
        @schedule_name = @schedule_name, -- avoid mismatch in uid if schedule was deleted and recreated
        @collection_mode = @collection_mode, 
        @logging_level = 0,
        @description = @description;
END
ELSE
BEGIN    
    RAISERROR ('Creating system Collection Set "%s"...', 0, 1, @collection_set_name) WITH NOWAIT;
    EXEC dbo.sp_syscollector_create_collection_set
        @collection_set_uid = @collection_set_uid,
        @name = @collection_set_name, 
        @schedule_name = @schedule_name,
        @collection_mode = @collection_mode, 
        @days_until_expiration = @days_until_expiration, 
        @description = @description, 
        @logging_level = 0,
        @collection_set_id = @collection_set_id OUTPUT;
END

-- for localization of collection set name and description
UPDATE syscollector_collection_sets_internal
SET name_id = @name_id, description_id = @description_id
WHERE collection_set_uid = @collection_set_uid;

-- Add collection items
DECLARE @collection_item_name NVARCHAR(128);
DECLARE @collection_item_old_name NVARCHAR(128);
DECLARE @collection_item_id int;
DECLARE @frequency int;
DECLARE @parameters xml;

-- Item 1 - Query activity collection type
-- The name and description retrived via FORMATMESSAGE will not be localized for the initial mkmastr build of msdb, 
-- but that is OK; the public interface to collection item detils (syscollector_collection_items) is a view that will 
-- dynamically pull the correct localized strings via its own FORMATMESSAGE calls.  We just need a unique string to 
-- store in the table.
SET @name_id = 14710;
SET @collection_item_name = FORMATMESSAGE(@name_id);
SET @collection_item_old_name = 'Query Statistics - Query Activity';
SET @frequency = 10;  

IF ISNULL (@collection_item_name, '') = '' 
BEGIN
    SET @collection_item_name = @collection_item_old_name;
END;

SET @collection_item_id = NULL;
SELECT @collection_item_id = collection_item_id
FROM syscollector_collection_items_internal 
WHERE collection_set_id = @collection_set_id 
    AND (name = @collection_item_name OR name = @collection_item_old_name);

IF (@collection_item_id IS NOT NULL)
BEGIN
    RAISERROR ('Updating Collection Item "%s"...', 0, 1, @collection_item_name);
    EXEC dbo.sp_syscollector_update_collection_item 
        @collection_item_id = @collection_item_id, 
        @new_name = @collection_item_name, 
        @frequency = @frequency, 
        @parameters = @parameters;
END
ELSE
BEGIN
    RAISERROR ('Creating Collection Item "%s"...', 0, 1, @collection_item_name);
    EXEC dbo.sp_syscollector_create_collection_item
        @collection_set_id = @collection_set_id,
        @collector_type_uid = N'14AF3C12-38E6-4155-BD29-F33E7966BA23',
        @name = @collection_item_name,
        @parameters = @parameters,
        @frequency = @frequency, 
        @collection_item_id = @collection_item_id output;
END;

-- for localization of collection item name
UPDATE syscollector_collection_items_internal
SET name_id = @name_id
WHERE collection_item_id = @collection_item_id;

-- Turn the is_system flag on so users can't change the definition of this collection set 
UPDATE syscollector_collection_sets
SET is_system = 1
WHERE collection_set_id = @collection_set_id
GO

-- End of installation of out-of-the-box collector components
-- Restore agent xp settings to original state
DECLARE @advopt_old_value int
DECLARE @comp_old_value int
SELECT @advopt_old_value = advopt_old_value FROM #advopt_old_value
SELECT @comp_old_value = comp_old_value FROM #comp_old_value

EXECUTE #sp_restore_component_state 'Agent XPs', @advopt_old_value, @comp_old_value

