SQL Replication Fails with an error for UnmodeledData

Version 2

    Verified Product Versions

    Endpoint Manager 9.6Endpoint Manager 2016.xEndpoint Manager 2017.xEndpoint Manager 2018.x

    Description: SQL Replication may fail with an error similar to the one below.



    The process could not bulk copy into table '"dbo"."UNMODELEDDATASnapshot"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)

    Get help: http://help/MSSQL_REPL20037

    Batch send failed

    Violation of UNIQUE KEY constraint 'UQ_UnmodeledData'. Cannot insert duplicate key in object 'dbo.UNMODELEDDATA'. The duplicate key value is (4210706, 6952, <NULL>, 648beee3-0671-446c-92e3-76951f4a4dc5). (Source: MSSQLServer, Error number: 2627)

    Get help: http://help/2627

    To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below. Consult the BOL for more information on the bcp utility and its supported options. (Source: MSSQLServer, Error number: 20253)

    Get help: http://help/20253

    bcp "LDMS_ROLLUP"."dbo"."UNMODELEDDATASnapshot" in "\\CORESERVER\ReplData\unc\CORESERVER\20150811103502\UNMODELEDDATACSO_255.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -SMAD-REAL-LDDB -T -w (Source: MSSQLServer, Error number: 20253)



    1- On the rollup database Run SELECT * FROM METASYSTEMS

    2- Find the SYSTEMGUID for the core server affected by the error and copy it.

    3- Enter the SYSTEMGUID in the following statement (aka..replace the X's) and run it on the rollup database: 'DELETE FROM UNMODELEDDATA WHERE CoreGUID = 'XXXXXXXXXXXX'


    Cause: The SQL Replication Utility isn't cleaning up previous entries. A fix for this will be released in a future patch. Manually cleaning up the table will correct the problem and allow replication to continue.