Avatar of enigmasolutions
enigmasolutions
 asked on

SQL 2005 - IsFullTextInstalled does not work

The script below returns the following error on SQL 2005... why??? (works fine in 2008)

"Full-Text Search is not enabled for the current database. Use sp_fulltext_database to enable full-text search for the database. The functionality to disable and enable full-text search for a database is deprecated. Please change your application"

And here is the script...

IF (NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = 'FT_Stock'))
AND exists (select * from (select SERVERPROPERTY('IsFullTextInstalled') as test) x where test = 1 )
Begin
exec('CREATE FULLTEXT CATALOG FT_Stock WITH ACCENT_SENSITIVITY = ON')
End
Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
enigmasolutions

8/22/2022 - Mon
Lowfatspread

what is the compatabiliy levels of the databases in both cases....?


is it possible that the sql 2008 instance db is running as sql 2005? AND A WARNING IS NO LONGER DISPLAYED?

Which line actually produces the error?

And is it actually an error or just a warning?
Anthony Perkins

The script below returns the following error on SQL 2005... why??? (works fine in 2008)
You can set that in SQL Server 2008, but it is ignored.  It is no longer relevant.
enigmasolutions

ASKER
Lowfatspread,  The CREATE FULLTEXT CATALOG is the line that raises the error in SQL 2005

acperkins,  Sorry I don't quite understand your answer.  Can you please elaborate?  Don't you have to "CREATE FULLTEXT CATALOG" to be able to use Full Text Searching?


More info...

We have a software program running on hundreds of different customer sites (running various versions of SQL).  We are trying to introduce Full Text Search "where possible" - ie where installed/enabled.  I cannot login manually to each Server to set it up.  My scriipt above is the first part of setting it up.  I believe that I DO need to create the Catalog first.

So my script needs to be compatible with all versions of SQL AND where FullText is not available the script need to be ignored - hence the IF statement.

NOTE - after running the script above I have another script to "ALTER FULLTEXT ON STK ENABLE" and another script to "CREATE FULLTEXT INDEX..." (and these scripts have similar IF statements).  But I don't get this far in 2005.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
enigmasolutions

ASKER
OK then I have 2 questions:

A) is there a script to tell which version of SQL is running

B) Do you know the syntax for what I want to do in 2005 - ie test if FullText is "available" and if so then create a catalog
enigmasolutions

ASKER
BTW Thanks for your answer - clears up the mystery a bit
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
enigmasolutions

ASKER
Awesome, thanks for your help.

Here is the final script & it works in 2005 and 2008


DECLARE @IsFullTextOK bit
DECLARE @SQLVER varchar(10)

SELECT @SQLVER= LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),2)

if @SQLVER='9.'
  SELECT @IsFullTextOK = cast(DATABASEPROPERTY( DB_NAME(), 'IsFulltextEnabled') as bit)
else
  SELECT @IsFullTextOK = cast(SERVERPROPERTY('IsFullTextInstalled') as BIT)
 
IF (@IsFullTextOK=1)
AND (NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = 'FT_Stock'))
Begin
  exec('CREATE FULLTEXT CATALOG FT_Stock WITH ACCENT_SENSITIVITY = ON')
End
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
enigmasolutions

ASKER
Fantastic solution