The following error occurred during SP1 update of Microsoft System Center 2012 Configuration Manager:
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:
- Note the CollectionID (16777228)
- Open SQL Management Studio, open SCCM Database
- Use this query to delete the rows in the table:
DELETE FROM CollectionQueryRuleTables WHERE CollectionID = [YourCollectionID]
- Run your update again.
- 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.
Hallo Norman
I have a similar problem to the one listed in the link below but with a few differences
http://www.normanbauer.com/2013/02/05/cannot-insert-duplicate-key-error-while-upgrading-sccm-2012-to-sp1/
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
Micheal