I'm getting an error with the SCCM 2012 Upgrade Assessment Tool when it tries to synchronize

SMS_UPGRADE_ASSESSMENT_POINT Error: 0 : Wed, 10 Jul 2013 20:15:06 GMT:Failed to merge with error: ExecuteNonQuery: Exception:The MERGE statement conflicted with the FOREIGN KEY constraint "UAT_ApplicationReport_DeploymentOS_FK". The conflict occurred in database "CM_A01", table "dbo.UAT_Deployment_OS", column 'OSID'.

I've got ACT installed and configured, SCCM 2012 SP1 with CU1 installed and everything is successfully verified within the UAT itself, but whenever it syncs I get this error...

Hi Darren,

I'm having the same problem.  Have you been able to get more information on this?

Thanks,

Dan

Hi Dan, I re-wrote a stored procedure in the ACT database to resolve this. When I'm back at my desk I'll post the fix. Cheers

Thanks Darren.  I hacked my way into it by removing the key restraint.  It is running fine now but modifying the SP would definitely be a better way forward!

Dan

Hey Dan - so I was wrong - the stored proc is in the CM database - it's called "sp_UATCreateTempTable". I added a line to this SP so that it would properly account for the missing foreign key (6.0.1, Windows Vista SP1/SP1)

This worked for me but of course, no guarantees here and obviously take appropriate steps when modifying the CM database. The ALTER command is below and I've bolded the line that I added:

USE [CM_A01]
GO

/****** Object:  StoredProcedure [dbo].[sp_UATCreateTempTable]    Script Date: 2013-08-20 10:11:37 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--
-- Name         : sp_UATCreateTempTable
-- Definition   : SqlObjs
-- Scope        : CAS_OR_PRIMARY
-- Object       : P
-- Dependencies : <Detect>
-- Description  : <Please provide a description for this object>
--
ALTER PROCEDURE [dbo].[sp_UATCreateTempTable]
    @TableName SYSNAME,
    @TempTableName SYSNAME
AS
BEGIN
    DECLARE @CREATETEMPTABLESTATEMENT NVARCHAR(MAX)
    -- Create temp table for UAT_Deployment_OS
    IF QUOTENAME(@TableName) = '[UAT_Deployment_OS]'
    SET @CREATETEMPTABLESTATEMENT =
        ' IF OBJECT_ID(''tempdb.dbo.' + QUOTENAME(@TempTableName) + ''') IS NOT NULL ' +
            ' DROP TABLE ' + QUOTENAME(@TempTableName) +
        ' CREATE TABLE ' + QUOTENAME(@TempTableName) +
        ' (OSID NVARCHAR(32), DisplayName NVARCHAR(50), Enabled BIT, PRIMARY KEY (OSID)) ' +
  'INSERT INTO ' + QUOTENAME(@TempTableName) +
  'VALUES(''6.0.1'',''Windows Vista SP1/SP1'',1)'

    -- Create temp table for UAT_Application
    ELSE IF QUOTENAME(@TableName) = '[UAT_Application]'
    SET @CREATETEMPTABLESTATEMENT =
        ' IF OBJECT_ID(''tempdb.dbo.' + QUOTENAME(@TempTableName) + ''') IS NOT NULL ' +
            ' DROP TABLE ' + QUOTENAME(@TempTableName) +
        ' CREATE TABLE ' + QUOTENAME(@TempTableName) +
        ' (AppIdentity INT, AppName NVARCHAR(260), Type NVARCHAR(11), VendorName NVARCHAR(260), Version NVARCHAR(50), Language INT, PRIMARY KEY (AppIdentity)) '
    -- Create temp table for UAT_Application_Report
    ELSE IF QUOTENAME(@TableName) = '[UAT_Application_Report]'
    SET @CREATETEMPTABLESTATEMENT = 
        ' IF OBJECT_ID(''tempdb.dbo.' + QUOTENAME(@TempTableName) + ''') IS NOT NULL ' +
            ' DROP TABLE ' + QUOTENAME(@TempTableName) +
        ' CREATE TABLE ' + QUOTENAME(@TempTableName) +
        ' ([OSID] NVARCHAR(32), AppIdentity INT, MyRating32 INT, MyRating64 INT, VendorRating32 INT, VendorRating64 INT, MicrosoftRating32 INT, MicrosoftRating64 INT, NumCommGreen32 INT, NumCommGreen64 INT, NumCommYellow32 INT, NumCommYellow64 INT, NumCommRed32 INT, NumCommRed64 INT, PRIMARY KEY (OSID, AppIdentity)) '
    -- Create temp table for UAT_MachineS
    ELSE IF QUOTENAME(@TableName) = '[UAT_MachineS]'
    SET @CREATETEMPTABLESTATEMENT = 
        ' IF OBJECT_ID(''tempdb.dbo.' + QUOTENAME(@TempTableName) + ''') IS NOT NULL ' +
            ' DROP TABLE ' + QUOTENAME(@TempTableName) +
        ' CREATE TABLE ' + QUOTENAME(@TempTableName) +
        ' (SmsGuid NVARCHAR(41), PRIMARY KEY (SmsGuid)) '
    -- Create temp table for UAT_MachineS_Installed_App
    ELSE IF QUOTENAME(@TableName) = '[UAT_MachineS_Installed_App]'
    SET @CREATETEMPTABLESTATEMENT = 
        ' IF OBJECT_ID(''tempdb.dbo.' + QUOTENAME(@TempTableName) + ''') IS NOT NULL ' +
            ' DROP TABLE ' + QUOTENAME(@TempTableName) +
        ' CREATE TABLE ' + QUOTENAME(@TempTableName) +
        ' (SmsGuid NVARCHAR(41), AppIdentity INT, PRIMARY KEY (SmsGuid, AppIdentity)) '
    -- Create temp table for UAT_Device_Status
    ELSE IF QUOTENAME(@TableName) = '[UAT_Device_Status]'
    SET @CREATETEMPTABLESTATEMENT = 
        ' IF OBJECT_ID(''tempdb.dbo.' + QUOTENAME(@TempTableName) + ''') IS NOT NULL ' +
            ' DROP TABLE ' + QUOTENAME(@TempTableName) +
        ' CREATE TABLE ' + QUOTENAME(@TempTableName) +
        ' (OSID NVARCHAR(32), Name NVARCHAR(255), RatingType32 INT, RatingType64 INT, SolutionType32 INT, SolutionType64 INT, SolutionURL32 NVARCHAR(1000), SolutionURL64 NVARCHAR(1000), Status32 INT, Status64 INT, PRIMARY KEY (OSID, Name)) '
    EXEC (@CREATETEMPTABLESTATEMENT)
END

GO

 

• Proposed as answer by Garth JonesMVP, Moderator Saturday, February 21, 2015 3:22 PM

FYI Keep in mind that editing the database is unsupported and the issue may pop-up again when you apply a SP or CU.