Error Creating Unique Index on ReplPRODUCTV

Version 2

    Verified Product Versions

    LANDESK Management Suite 9.6LANDESK Management Suite 2016.x

    When setting up a Rollup Core you may run into an event where you get the following message:

     

    To understand why this error occurs you have to know the process/architecture of how replication is set up. The first thing that we do is try to create a view for every table in the database. This view is used to copy data to the Rollup database. Once the view is created we then have to apply various Index's to ensure that the data is unique for each child database as the data is copied to the Rollup core's database and is consolidated/normalized into it's own set of data with unique ID's. The error above is telling us that the data located on your child core is not unique and as a result it cannot have a unique index applied to it (in this case it can't apply the index to the view cause of duplicate listings).

     

    This is pretty easy to find using the following script:

     

    select title, version, productguid, dadefined, coreguid, max(product_idn) as idn, count(*) 
    from ReplPRODUCTV 
    group by title, version, productguid,dadefined, coreguid
    having count(*) > 1
    

     

    This script will show you the results from the ReplPRODUCTV itself. However, you may not have this view created on in your database yet, but may still see this same error. If that's the case you can change the query so that it joins the data together the same way the view would:

     

    select title, version, productguid,dadefined, coreguid, max(product_idn) as idn, count(*) 
    from (SELECT isnull(b.Product_Idn, 0) Product_Idn, isnull(b.Title, 0) Title, isnull(b.Version, '') Version, b.Publisher, b.Action, b.FileMatching, isnull(b.ProductGUID, '') ProductGUID, b.LDMSProduct, b.Discovered, b.Ignore, isnull(b.Manufacturer_Idn, 0) Manufacturer_Idn, b.LicenseSuite_Idn, b.MainExe, b.OS, isnull(b.DataSourceType, 0) DataSourceType, b.OriginalID, b.Orig_Manufacturer_Idn, b.CustomGroupIdn, b.QueryIdn, isnull(b.DADefined, 0) DADefined, isnull(cast(a.SYSTEMGUID as uniqueidentifier), cast(cast(0 as binary) as uniqueidentifier)) COREGUID
    FROM dbo.METASYSTEMS a, dbo.PRODUCT b
    WHERE a.SYSTEM_IDN = 0) as replproducttemp 
    group by title, version, productguid,dadefined, coreguid
    having count(*) > 1
    

     

    **Additional Info: If you're attempting to adapt this for another view with duplicate issues all I did to create the previous SQL script was the following:

    1. find the view in a known working rollup environment and using SQL Management Studio's Object Explorer
    2. Right-Click on the view and select Script View As.. > CREATE To.. > New Query Editor Window
    3. At this point I just grabbed the select statement used to create the view and used it as a temp table in the from clause of my SQL Statement

     

    Now you should have a list of duplicate software. Once you have this, please report the information to Support via the Support Portal so that we can address it as a defect.

     

    But now that we know what the duplicates are we can remove them from your database by adapting the previously used script into a cursor like so:

     

    declare product cursor for
    select title, version, productguid,dadefined, coreguid, max(product_idn) as idn, count(*) 
    from (SELECT isnull(b.Product_Idn, 0) Product_Idn, isnull(b.Title, 0) Title, isnull(b.Version, '') Version, b.Publisher, b.Action, b.FileMatching, isnull(b.ProductGUID, '') ProductGUID, b.LDMSProduct, b.Discovered, b.Ignore, isnull(b.Manufacturer_Idn, 0) Manufacturer_Idn, b.LicenseSuite_Idn, b.MainExe, b.OS, isnull(b.DataSourceType, 0) DataSourceType, b.OriginalID, b.Orig_Manufacturer_Idn, b.CustomGroupIdn, b.QueryIdn, isnull(b.DADefined, 0) DADefined, isnull(cast(a.SYSTEMGUID as uniqueidentifier), cast(cast(0 as binary) as uniqueidentifier)) COREGUID
    FROM dbo.METASYSTEMS a, dbo.PRODUCT b
    WHERE a.SYSTEM_IDN = 0) as replproducttemp 
    group by title, version, productguid,dadefined, coreguid
    having count(*) > 1
    
    
    declare @title varchar(max)
    declare @version varchar(max)
    declare @productguid varchar(max)
    declare @dadefined varchar(max)
    declare @coreguid varchar(max)
    declare @idn int
    declare @count int
    declare @licid int
    open product
    fetch next from product into @title, @version, @productguid, @dadefined, @coreguid, @idn, @count
    while @@FETCH_STATUS = 0 
    begin
      delete from SLM_ProductUsageFile where Product_Idn = @idn
      delete from SLM_LicenseProductREF where SLM_SoftwareLicense_Idn in (select SLM_SoftwareLicense_Idn from SLM_SoftwareLicense where PriProdAssociation = @idn)
      delete from SLM_SoftwareLicense where PriProdAssociation = @idn
      delete from SLM_LicenseProductREF where product_idn = @idn
      delete from product where product_idn = @idn
      fetch next from product into @title, @version, @productguid, @dadefined, @coreguid, @idn, @count
    end
    close product
    deallocate product
    

     

    **Additional Note: You may run into constraint errors with this script. These are really easy to fix if you review the error message and compare the data. The error will always say what table and column that it's conflicting with, it's important to check the table the error is mentioning and make sure that the column has the ID's mentioned in the script we used previously that displayed the products and their MAX idn's. Once you've confirmed that information just add another delete statement to the top to of the delete statements and you should be on your way.

     

    The script above will remove all the duplicates from the database and allow the replication utility to complete it's setup process for sql replication.