Cannot insert duplicate key error while upgrading SCCM 2012 to SP1

Posted by on Feb 5, 2013 in SC Configuration Manager, SQL Server, Transact-SQL | 1 comment

The following error occurred during SP1 update of Microsoft System Center 2012 Configuration Manager:

SCCM 2012 SP1 Cannot insert duplicate key error

ERROR: SQL Server error: [23000][2627][Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY constraint ‘CollectionQueryRuleTables_PK’. Cannot insert duplicate key in object ‘dbo.CollectionQueryRuleTables’. The duplicate key value is (16777228, 1, vSMS_R_System). Configuration Manager Setup 05.02.2013 09:12:18 4084 (0x0FF4)

The easiest way would be to delete the corresponding rows from CollectionQueryRuleTables table in your SCCM database. This would solve the problem but it is not supported by Microsoft:

  1. Note the CollectionID (16777228)
  2. Open SQL Management Studio, open SCCM Database
  3. Use this query to delete the rows in the table:
    DELETE FROM CollectionQueryRuleTables WHERE CollectionID = [YourCollectionID]
  4. Run your update again.
  5. Test if the collection still works. You can determine the collection by running this query:
    SELECT * FROM Collections_G WHERE CollectionID = [YourCollectionID]

    CollectionName is the name of the collection in SCCM.

Again: Editing the database directly is not supported. Make a backup of your database before you start! Don’t blame me if something goes wrong.

One Comment

  1. Hallo Norman
    I have a similar problem to the one listed in the link below but with a few differences

    there were no upgrades that we did on the site server
    also in the SMSProv.log I actually get the “Duplicate key value” as the collection ID – IMP000012
    When I run the sql above I get the following error in SQL
    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value ‘IMP00012’ to data type int.

    I also get this when I try to create a new application/software package
    any other suggestions please let me know
    kind regards

Leave a Reply

Your email address will not be published. Required fields are marked *

Captcha: * Time limit is exhausted. Please reload CAPTCHA.