Link to home
Start Free TrialLog in
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
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

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?
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.
Avatar of enigmasolutions
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.
SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
BTW Thanks for your answer - clears up the mystery a bit
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Fantastic solution