Cannot open/create knowdlege base after applying CU4

Question

I am runnign into some issues when trying to open or creake KBs, afetr appying CU4 where DQ client reports the following error:

Execution of DB script 'Microsoft.Ssdqs.Index.TSQLKB.StoredProcedures.NewIndexParseTrigramsFromLexicon.sql' failed.

DQS Logs shows that the faulty TSQL is referencing and invalid type called DQProjectDomainIdsListTable

10/30/2012 7:51:12 AM|[]|6|ERROR|27c437ac-e6af-40f8-ae64-5e199f52705e|Microsoft.Ssdqs.Core.Service.KnowledgebaseManagement.Managers.DBSchemaManager|Execution of DB script 'Microsoft.Ssdqs.Index.TSQLKB.StoredProcedures.NewIndexParseTrigramsFromLexicon.sql' failed. Failing batch: 
CREATE PROCEDURE [KnowledgeManagement1000022].[NewIndexParseTrigramsFromLexicon] ;
 @DomainIdsList DQProjectDomainIdsListTable READONLY,;
 @ConsiderExistingTrigrams BIT;
AS;
BEGIN;


    IF @ConsiderExistingTrigrams = 0;
        INSERT INTO X_TRIGRAM_TERM_RELATION WITH(XLOCK, TABLOCK, HOLDLOCK) (TERM_ID, TRIGRAM, REPETITION_COUNT);
        SELECT ;
            Lexicon.ID AS TERM_ID, ;
            Trigrams.Trigram AS TRIGRAM, ;
            Trigrams.Repetitions AS REPETITION_COUNT;
        FROM B_INDEX_LEXICON AS Lexicon WITH(NOLOCK);
        CROSS APPLY [DQS_MAIN].internal_core.CreateTrigramsFromString(Lexicon.ALGORITHM_VALUE) AS Trigrams;
        WHERE ;
            Lexicon.ID >= 0 AND LEXICON_FIELD_ID IN (SELECT n from @DomainIdsList);
        OPTION(MAXDOP 1);
    ELSE;
        INSERT INTO X_TRIGRAM_TERM_RELATION WITH(XLOCK, TABLOCK, HOLDLOCK) (TERM_ID, TRIGRAM, REPETITION_COUNT);
        SELECT ;
            Lexicon.ID AS TERM_ID, ;
            Trigrams.Trigram AS TRIGRAM, ;
            Trigrams.Repetitions AS REPETITION_COUNT;
        FROM B_INDEX_LEXICON AS Lexicon WITH(NOLOCK);
        CROSS APPLY [DQS_MAIN].internal_core.CreateTrigramsFromString(Lexicon.ALGORITHM_VALUE) AS Trigrams;
        WHERE ;
            Lexicon.ID >= 0 AND LEXICON_FIELD_ID IN (SELECT n from @DomainIdsList) AND;
            NOT EXISTS (SELECT TOP(1) 1 FROM X_TRIGRAM_TERM_RELATION AS Trigram WITH(XLOCK, TABLOCK, HOLDLOCK) WHERE Lexicon.ID = Trigram.TERM_ID);
        OPTION(MAXDOP 1);
END
System.Data.SqlClient.SqlException (0x80131904): Column, parameter, or variable #1: Cannot find data type DQProjectDomainIdsListTable.;
Must declare the table variable "@DomainIdsList".;
Must declare the table variable "@DomainIdsList".;
Parameter or variable '@DomainIdsList' has an invalid data type.;

This occurs afetr applying CU4 and running. As part of that I ran

DQSInstaller.exe -upgrade and

DQSInstaller.exe -upgradedlls

but both of them reported error.

I appreciate your help.

---

Help Others! Don't forget to mark your thread as answered

Answer 1

Hey Rafael!

What is the full scenario?  Did the computer have DQS already before CU4, so you are upgrading, or is this a fresh install of DQSInstaller (use no switches for that scenario)?

Looks like the error is

System.Data.SqlClient.SqlException (0x80131904): Column, parameter, or variable #1: Cannot find data type DQProjectDomainIdsListTable.;

In the DQS installer logs, do you see this output? Usually the custom type is added by DQS Installer. Log excerpt should say:

[9/30/2012 5:08:54 AM] Changed database context to 'DQS_PROJECTS'.
[9/30/2012 5:08:54 AM] ---[Add DQProjectDomainIdsListTable table type]---

The log goes into C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DQS_install.log

Thx, Jason

---

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

Answer 2

Did you get this resolved?

To fix the issue you can now uninstall the CU4 and install SP1 that was released 6 November 2012

Answer 3

Thanks for the suggetsion Neofaster.

I ended up unistalling/re-installing the whole SQL Server instance as troubleshooting steps were taking me nowhere and I was working on a demo environement. Installing/reinstalling DQS only did not work so I went down a more drastic path of re-oing the instance.

---

Help Others! Don't forget to mark your thread as answered