Full text index error

  • Kurt-637727

    Old Hand

    Points: 365

    Hi,

    I build some t-sql code to check if full text is installed on the sql server. If not, some sql statements must be not executed. Here is my code:

    if (select serverproperty('IsFullTextInstalled')) = 1

    Begin

    EXEC sp_fulltext_database 'enable'

    CREATE FULLTEXT CATALOG [...] WITH ACCENT_SENSITIVITY = OFF AS DEFAULT

    CREATE FULLTEXT INDEX ON dbo.Test (Name LANGUAGE 0, Description LANGUAGE 0) KEY INDEX IX_Test_1 ON [...] WITH CHANGE_TRACKING AUTO

    ALTER FULLTEXT INDEX ON dbo.Test ENABLE

    End

    Statement 1 and 2 is not executed, but for statement 3 the server throws the following error:

    "Full-Text Search is not installed, or a full-text component cannot be loaded."

    I don't know why the server tries to execute statement 3, because it is in an if statement.

    Any help is welcome.

  • antonio.collins

    SSCrazy Eights

    Points: 9600

    are you certain the CREATE FULLTEXT CATALOG statement is not producing error?

    you can enable full text indexing for the database even if full text search is not installed, but CREATE FULLTEXT CATALOG will fail with msg 7609 if it's not installed.

  • Kurt-637727

    Old Hand

    Points: 365

    Yes, I'm sure about that.

    It's the line Create Full Text index that produce the error. But I don't understand why the first 2 lines are ignored (by using the if statement) and the third line not. Has it something to do with compiling of the sql statement?

  • antonio.collins

    SSCrazy Eights

    Points: 9600

    the create catalog should fail if full text indexing is not available. does the sql below run without error?

    if (1 = 1)

    begin

    exec sp_fulltext_database 'enable'

    create fulltext catalog testcat

    drop fulltext catalog testcat

    end

  • Kurt-637727

    Old Hand

    Points: 365

    The sql server throws an error for this query statement. That's normal, because full text indexing is not installed. It fails on the line create fulltext catalog and throws the same error as above: "Full-Text Search is not installed, or a full-text component cannot be loaded."

    What I don't understand is why the create fulltext catalog line does not fail and the create fulltext index fails in my original t-sql. Because, when full text indexing is not installed, these sql statements must be ignored (because of the if statement).

  • antonio.collins

    SSCrazy Eights

    Points: 9600

    Kurt (2/12/2008)


    The sql server throws an error for this query statement. That's normal, because full text indexing is not installed. It fails on the line create fulltext catalog and throws the same error as above: "Full-Text Search is not installed, or a full-text component cannot be loaded."

    What I don't understand is why the create fulltext catalog line does not fail and the create fulltext index fails in my original t-sql. Because, when full text indexing is not installed, these sql statements must be ignored (because of the if statement).

    the above test proves that in your original post, the create fulltext catalog was failing. comment out your index statement and i'll bet you still get the error.

    but your if statement only checks if sql server thinks fulltext search is installed, not whether or not it's actually up and running. fulltext search is a seperate service and can be enabled/disabled outside of sql server and sometimes it can fail on startup for various reasons.

  • Kurt-637727

    Old Hand

    Points: 365

    I put the create fulltext index in comment. I don't get an error. I get only an error when the lines create fulltext index is in the if statement.

    Of course, full text is a seperate service. I only want to know if the implementation of sql server has the full text indexing service installed. If not, I don't want to execute the sql statement for creating the catalog, index, ...

  • antonio.collins

    SSCrazy Eights

    Points: 9600

    so, you're saying that this block executes without error:

    if (select serverproperty('IsFullTextInstalled')) = 1

    Begin

    EXEC sp_fulltext_database 'enable'

    CREATE FULLTEXT CATALOG [...]

    WITH ACCENT_SENSITIVITY = OFF AS DEFAULT

    /*

    CREATE FULLTEXT INDEX ON dbo.Test

    (Name LANGUAGE 0, Description LANGUAGE 0)

    KEY INDEX IX_Test_1 ON [...] WITH CHANGE_TRACKING AUTO */

    ALTER FULLTEXT INDEX ON dbo.Test ENABLE

    End

    this block fails with a full text not available error:

    if (select serverproperty('IsFullTextInstalled')) = 1

    Begin

    EXEC sp_fulltext_database 'enable'

    CREATE FULLTEXT CATALOG [...]

    WITH ACCENT_SENSITIVITY = OFF AS DEFAULT

    CREATE FULLTEXT INDEX ON dbo.Test

    (Name LANGUAGE 0, Description LANGUAGE 0)

    KEY INDEX IX_Test_1 ON [...] WITH CHANGE_TRACKING AUTO

    ALTER FULLTEXT INDEX ON dbo.Test ENABLE

    End

    and this block fails with a full text not available error:

    if (1 = 1) -- i assume serverproperty('IsFullTextInstalled') = 1

    begin

    exec sp_fulltext_database 'enable'

    create fulltext catalog testcat

    drop fulltext catalog testcat

    end

    it's not possible to create a fulltext catalog if fulltext search is not available.

  • Kurt-637727

    Old Hand

    Points: 365

    The last one fails only when Full text indexing is not installed. And that's normal.

    I have 2 SQL Servers. One sql server that has full text indexing installed, another one where full text indexing is NOT installed.

    My query in the first post has no problem on the server with full text indexing installed. The same query fails on the sever where full text indexing is NOT installed.

    I look for a solution to let my query work on the server where full text indexing is NOT installed.

  • antonio.collins

    SSCrazy Eights

    Points: 9600

    what's the value of serverproperty('IsFullTextInstalled') on the server without fulltext indexed?

    i still think there's something odd about your original post, since the create fulltext catalog should have failed. but if you just want to move ahead, use try-catch blocks instead.

    Begin Try

    EXEC sp_fulltext_database 'enable'

    CREATE FULLTEXT CATALOG [...]

    WITH ACCENT_SENSITIVITY = OFF AS DEFAULT

    CREATE FULLTEXT INDEX

    ON dbo.Test (Name LANGUAGE 0, Description LANGUAGE 0)

    KEY INDEX IX_Test_1 ON [...] WITH CHANGE_TRACKING AUTO

    ALTER FULLTEXT INDEX ON dbo.Test ENABLE

    End Try

    Begin Catch

    -- logic if any fulltext statements failed

    End Catch;

  • Kurt-637727

    Old Hand

    Points: 365

    The serverproperty('IsFullTextInstalled') returns 0 on the server without full text indexing.

    Unfortunately, the solution that you proposed does not work either. The server returns again the error message: "Full-Text Search is not installed, or a full-text component cannot be loaded." And it is again the line of the create fulltext index that produces the error.

  • antonio.collins

    SSCrazy Eights

    Points: 9600

    you only posted pseudo-code (CATALOG [...] WITH ) so this might be a case of the 'real' code differing slightly from the posted example. and now you're stating that although serverproperty('IsFullTextInstalled') is zero, the if block below is still executing.

    if (select serverproperty('IsFullTextInstalled')) = 1

    Begin

    ... fulltext index statements ...

    there must be something wrong with your if block. try this simplification instead:

    if (serverproperty('IsFullTextInstalled') = 1)

    begin

    print 'full text is installed'

    ... fulltext index statements ...

    end

  • Kurt-637727

    Old Hand

    Points: 365

    Ok, on this forum I put pseudo code. But I test it with real code...

    And yes, even the return value is 0 the create fulltext index is executed or compiled. I don't know why.

  • lukezy

    Newbie

    Points: 9

    I had the exact same issue and worked around this by using dynamic sql. Try this.

    IF (FullTextServiceProperty('IsFullTextInstalled') = 1)

    EXECUTE ('CREATE FULLTEXT CATALOG [...] WITH ACCENT_SENSITIVITY = OFF AS DEFAULT')

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply