February 11, 2008 at 9:33 am
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.
February 11, 2008 at 10:41 am
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.
February 11, 2008 at 11:32 am
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?
February 11, 2008 at 12:25 pm
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
February 12, 2008 at 1:17 am
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).
February 12, 2008 at 7:48 am
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.
February 12, 2008 at 7:58 am
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, ...
February 12, 2008 at 8:28 am
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.
February 12, 2008 at 8:37 am
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.
February 12, 2008 at 8:48 am
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;
February 12, 2008 at 9:08 am
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.
February 12, 2008 at 9:21 am
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
February 12, 2008 at 9:26 am
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.
June 23, 2010 at 3:29 pm
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 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply