Full Text Index error - Stored Procedures, Triggers, & Functions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » Stored Procedures, Triggers, & Functions » Full Text Index error

Full Text Index error

Options

Previous Topic · Next Topic

Kurt

Posted: Wednesday, February 13, 2008 5:06:21 AM

Rank: Newbie

Joined: 2/13/2008
Posts: 2
Points: 6
Location: Belgium

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.

This query fails only on a SQL Server where Full Text Indexing is NOT installed.

Any help is welcome

Scott Whigham

Posted: Wednesday, February 13, 2008 4:14:49 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Your code works as expected on my end. When you say "statement 3", which statement do you mean? The CREATE FULLTEXT INDEX?

You probably want to only do the sp_fulltext_database if DATABASEPROPERTYEX(DB_NAME(), 'IsFulltextEnabled')=0 as well.

Kurt

Posted: Thursday, February 14, 2008 4:09:19 AM

Rank: Newbie

Joined: 2/13/2008
Posts: 2
Points: 6
Location: Belgium

Yes, I mean the CREATE FULLTEXT INDEX.

Do you have tried my code on a sql server that does not have the full text indexing installed? When it is installed, my code works.

Scott Whigham

Posted: Saturday, February 16, 2008 6:22:14 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

I ran it on a server w/ full text installed. One thing that the serverproperty call does not tell you is whether the service is running (or disabled) - is that perhaps causing an error?

Here's the code that I ran with no errors:

Code:

create database testing
go
use testing
go
create table dbo.Test (ID INT NOT NULL IDENTITY(1,1) CONSTRAINT PK PRIMARY KEY, Name VARCHAR(128), Description VARCHAR(128))

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

EXEC sp_fulltext_database 'enable'

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

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

End

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.