Sql-server – Cannot Update a System Collection Set

data collectionsql serversql server 2014sql-server-2008-r2sql-server-2012

I'm trying to tweak the "Server Activity" Data Collection set as outlined in this blog post (I wrote the post, btw. Sorry–it's kinda long). It is working in SQL 2008 R2 and also in SQL 2014. However, when I run msdb.dbo.sp_syscollector_update_collection_item on SQL 2012, I get this error:

Msg 14696, Level 16, State 1, Procedure
sp_syscollector_update_collection_item, Line 70 Cannot update or
delete a system collection set, or add new collection items to it.

Looking at the code of msdb.dbo.sp_syscollector_update_collection_item, I see differences between the SQL versions:

SQL 2008 R2/SQL 2014

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

SQL 2012

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

I'm passing in a non-NULL value for @parameters, so I fully understand why I'm getting the error on SQL 2012.

Questions:

  1. Why is the code different for SQL 2012?
  2. Is it safe to manually change the code for msdb.dbo.sp_syscollector_update_collection_item?

Update: (version numbers for further clarification)

Ver     SP  Build Number
--------------------------
2008 R2 SP3 10.50.6000.34
2012    SP2 11.0.5058.0
2014    SP1 12.0.4100.1

Update2:
I've updated SQL 2012 to SP3 (11.0.6020.0)
The issue persists.

Best Answer

I think it's safe to say the or @parameters IS NOT NULL in SQL Server 2012 is a bug.

  • SQL Server 2012 v11.0.6260.1 also has the erroneous code.
  • SQL Server 2016 v13.0.4446.0 does not have that piece.
  • SQL Server 2017 v14.0.3025.34 does not have that piece.

Furthermore, the code prevents sp_syscollector_validate_xml from being called for non-system collection sets.

Since the stored procedure appears to be a system-stored-procedure, you'll likely need to use sp_MS_marksystemobject to mark the stored procedure as a system-stored-procedure after you modify it to remove the or @parameters IS NOT NULL code.

Be aware that changing procedures that have been shipped by Microsoft may make support more difficult. I would recommend against making this change on production-critical instances.

For reference, the code as seen in my SQL Server 2012 instance is: 

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

The code in SQL Server 2016 is:

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))
    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
Related Question