Installing DQS - foreign key conflict error

Question

Hi - Am getting below error when running DQS installer from start menu

8/11/2012 2:21:24 PM|[]|4|INFO|PUID|Microsoft.Ssdqs.Core.Startup.ServerInit|Starting DQS ServerInit: version [11.0.0.0], machine name [AHMAD-PC], user name [AHMAD-PC\Admin], operating system [Microsoft Windows NT 6.1.7601 Service Pack 1]...
8/11/2012 2:21:27 PM|[]|4|INFO|83b3bd57-0607-42d4-8e85-cd7967e8050b|Microsoft.Ssdqs.Core.Service.KnowledgebaseManagement.Managers.DBSchemaManager|Creating DB schema [DQS_PROJECTS].[TemplateSchema]
8/11/2012 2:21:29 PM|[]|4|ERROR|83b3bd57-0607-42d4-8e85-cd7967e8050b|Microsoft.Ssdqs.Core.Service.KnowledgebaseManagement.Managers.DBSchemaManager|Execution of DB script 'Microsoft.Ssdqs.Core.Service.KnowledgebaseManagement.TSQL.create_schema_template_data.sql' failed. Failing batch: 
INSERT INTO [DQS_MAIN].dbo.A_TEMPLATE_OBJECTS(ID, NAME, TYPE, SCOPE);
SELECT ;
    O.object_id AS ID,;
    O.name AS NAME,;
    O.type AS TYPE,;
    3 AS SCOPE;
FROM sys.objects AS O;
INNER JOIN sys.schemas AS S;
    ON O.schema_id = S.schema_id;
    AND S.name = 'TemplateSchema';
WHERE NOT EXISTS (SELECT TOP(1) 1 FROM [DQS_MAIN].dbo.A_TEMPLATE_OBJECTS AS A WHERE A.ID = O.object_id);

INSERT INTO [DQS_MAIN].dbo.A_TEMPLATE_FOREIGN_KEYS(ID, PARENT_ID, REFERENCE_ID);
SELECT ;
    O.object_id AS ID,;
    F.parent_object_id AS PARENT_ID,;
    F.referenced_object_id AS REFERENCE_ID;
FROM sys.objects AS O;
INNER JOIN sys.foreign_keys as F;
    ON F.object_id = O.object_id;
INNER JOIN [DQS_MAIN].dbo.A_TEMPLATE_OBJECTS AS A;
    ON A.ID = O.object_id;
    AND A.SCOPE = 3;

IF (3 & 1) <> 0;
BEGIN;
    INSERT INTO [DQS_MAIN].dbo.A_TEMPLATE_COLUMNS(NAME, TYPE, PARENT_ID);
    SELECT ;
        C.name AS NAME,;
        T.name AS TYPE,;
        O.object_id AS PARENT_ID;
    FROM sys.objects AS O;
    INNER JOIN sys.columns as C;
        ON C.object_id = O.object_id;
    INNER JOIN sys.types as T;
        ON T.system_type_id = C.system_type_id ;
        AND T.user_type_id = C.user_type_id;
        AND O.TYPE = 'U';
END
System.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "A_TEMPLATE_COLUMNS_PARENT_ID_FK". The conflict occurred in database "DQS_MAIN", table "dbo.A_TEMPLATE_OBJECTS", column 'ID'.;

Need help !!!

---

Cheers!!! SqlFrenzy

Answer 1

1. I haven't seen this error to date yet, so its a new one for me. Its failing because the IDs aren't lining up between the two tables. The list of columns have a parent ID listed for each, and that needs to have a corresponding ID in the template objects table.

A_TEMPLATE_COLUMNS(PARENT_ID) --> A_TEMPLATE_OBJECTS (ID)

2. Did the DQS_MAIN database get deleted already?

You may need to delete the 2 databases, and retry the DQSInstaller.exe.

3. What collation is your server set to? In Management Studio object explorer, right click on the server name > Properties and on the General page, there is a setting Server Collation.

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance