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.