Cannot Update a System Collection Set

1 vote

1 answer

437 views

sql-server sql-server-2008-r2 sql-server-2012 sql-server-2014 data-collection

                          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.
                        

Asked by Dave Mason (875 rep)

Jul 7, 2016, 07:04 PM
Last activity: Feb 9, 2021, 02:01 PM

Cannot Update a System Collection Set

Related Questions