DBCC CALLFULLTEXT -- sql server full text search

Carl Henthorn
1/27/2005 7:59:04 AM

Thanks!!!
How can I tell if my catalog is set up to track changes or not?
can you change that setting without having to delete the catalog and build a
new one?

[quoted text, click to view]

John Kane
1/27/2005 8:13:14 AM

You're welcome, Carl,
That's easy, you can use the fully supported FT Catalog metadata property:
FULLTEXTCATALOGPROPERTY - "Returns information about full-text catalog
properties", for example:

USE <your_database_name>
go
SELECT fulltextcatalogproperty('<your_FT_Catalog_Name>', 'PopulateStatus')

and if Change Tracking is turned on for this FT Catalog, it will return a
value of: 9. See SQL Server 2000 BOL title "FULLTEXTCATALOGPROPERTY":
Property Description
PopulateStatus 0 = Idle
1 = Full population in progress
2 = Paused
3 = Throttled
4 = Recovering
5 = Shutdown
6 = Incremental population in progress
7 = Building index
8 = Disk is full. Paused.
9 = Change tracking

Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

[quoted text, click to view]

Carl Henthorn
1/27/2005 8:51:02 AM

ok, I get a zero (idle) for my resultset for Populatestatus. Are you saying
that if Change tracking was turned on, that result would always be "9"?
change tracking is always on, right?
I could probably turn changetracking on by updating
master..sysfulltextcatalogs.status column. is that correct?
thanks!

[quoted text, click to view]

John Kane
1/27/2005 6:49:31 PM

Carl,
While I don't have a large table to test (at this time) this, but the
returned value of PopulateStatus may be set to 9 only when rows are
inserted/deleted/updated on your Change tracking enabled table during the
actual update of the FT Catalogs, so the value may depend upon when your
issue the query. Additionally, you can also use sp_help_fulltext_catalogs
and check the value of the status column as well.

However, I would NOT recommend changing the
master..sysfulltextcatalogs.status value as such a change could possibly
damage the functionality of your FT Catalog as well as its generally not
good practice to modify the systems tables without direct recommendations
from Microsoft.

Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

[quoted text, click to view]

simon
2/16/2005 7:06:03 PM

I'm trying to do something similar at the table level, which is test if
a table that I use for full-text search has the following "action"
properties enabled (as used in the sp_fulltext_table() proc):

activate
start_change_tracking
start_background_updateindex

The main reason is to avoid the errors thrown via JDBC when I try to
enable an action that is already enabled.

I could trap the error thrown, but it seems cleaner to test for the
state first.

Do you know how to test these states at the table level?

Also, do you know if there is any problem initiating a "start_full"
while change-tracking and background-update is enabled?

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***

John Kane
2/16/2005 8:52:01 PM

Simon,
The problem with this is that the actions you want to determine (activate,
start/stop populations, etc.) are done at the FT Catalog level as there can
be one or more tables in one FT Catalog, but only one FT Catalog to a table.
It's like saying your want to access a table with only knowing the database
name. However, you can determine if a table has a FT Catalog via
sp_help_fulltext_tables or sp_help_fulltext_tables_cursor without knowing
the FT Catalog name, for example:

sp_help_fulltext_tables null,'authors'
-- or
DECLARE @mycursor CURSOR
EXEC sp_help_fulltext_tables_cursor @mycursor OUTPUT, null, 'authors'
FETCH NEXT FROM @mycursor
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM @mycursor
END
CLOSE @mycursor
DEALLOCATE @mycursor
GO

Note, the NULL value for the FT Catalog name.

No, there is no problem with executing a "start_full" or Full Population
while change-tracking and background-update is enabled, but keep in mind
that all data is thrown out and when the Full Population is started.

Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

[quoted text, click to view]

simon
2/17/2005 9:18:30 AM

Thanks, but I'm still a little fuzzy on this.

"start_change_tracking" and "start_background_updateindex" appear to be
table level actions, since they are only controllable by
sp_fulltext_table(). I would assume there is some way to check the
state of these settings for each table, but I don't know where. I call
them "states" because I assume they run forever until you stop them.

"start_full" and "start_incremental" can be initiated at the catalog or
table level, since they are both actions in the sp_fulltext_catalog()
and sp_fulltext_table() procs. I assume they are not "states" per se,
but rather activities that have a finite lifetime. But similarly there
should be a way to monitor their progress at both the catalog and table
level, depending on which you initiated.

What I'm looking for is a way to see if any of the above are "active",
both at the table level and catalog level, if possible.

This brings up another question: If I initiate "start_change_tracking"
and "start_background_updateindex", and then later initiate "start_full"
or "start_incremental", does it halt the change-tracking and
background-update, or do those actions persist after the
full/incremental update finishes?

In other words, do I have to re-initiate the background updates after a
forced update?

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server full text search

sql server full text search : DBCC CALLFULLTEXT