|
|
You're in the
sql server full text search
group:how to set up Change Tracking
sql server full text search:
Hello,
I'm working on setting up full text search. What I want is to do background updating with Change Tracking. I have a timestamp column on my table. I have filled the index once like this: exec sp_fulltext_table @tabname = 'Copy', @action = 'start_full' How do I set it up to do background updating with Change Tracking now that 'start_full' has been peformed? Thanks a lot, Rogue Petunia
Rogue,
The first place to look for info on Change Tracking (other than this newsgroup) is SQL Server Books Online (BOL). You should review the following titles: "Full-text Search" (Accessing and Changing Relational Data), "Full-Text Search Recommendations", "Maintaining Full-Text Indexes" (Creating and Maintaining Databases). sp_fulltext_table, sp_fulltext_column and sp_help_fulltext_catalogs. Note, you do not have to run a Full Population prior to enabling CT with UIiB, as it will automatically schedule a Full Population for an un-populated FT Catalog or an Incremental Population for a populated FT Catalog (assuming that a timestamp is in the FT-enabled table, otherwise a Full Population is executed.). Also, it implement Change Tracking with "Update Index in Background" via pure T-SQL use: EXEC sp_fulltext_table '<FT_table>', 'Start_change_tracking' EXEC sp_fulltext_table '<FT_table>', 'Start_background_updateindex' go Regards, John [quoted text, click to view] "Rogue Petunia" <roguepetunia@NOSPAMnyc.rr.com> wrote in message news:ehfpnJirDHA.640@tk2msftngp13.phx.gbl... > Hello, > I'm working on setting up full text search. What I want is to do background > updating with Change Tracking. I have a timestamp column on my table. > > I have filled the index once like this: > exec sp_fulltext_table @tabname = 'Copy', @action = 'start_full' > > How do I set it up to do background updating with Change Tracking now that > 'start_full' has been peformed? > > Thanks a lot, > Rogue Petunia > > >
Rogue,
Sure... I think you're looking for the FTS metadata functions and help sp as this will give you the status or type of FT Indexing maintenance you are looking for... sp_help_fulltext_catalogs -- see STATUS column for 9 = Change tracking SELECT fulltextcatalogproperty('PubInfoFTC', 'PopulateStatus') -- returns: 9 for Change tracking Regards, John [quoted text, click to view] "Rogue Petunia" <roguepetunia@NOSPAMnyc.rr.com> wrote in message news:uBL8XyqrDHA.1888@TK2MSFTNGP10.phx.gbl... > John, > Another question, please. How can someone look at a Full Text Catalogue and > find out what index maintanence has been set up for it? I know CT with UliB > is set up on this one because I just did it, but how can another developer > find out this info? > > Thanks, > Rogue Petunia > > "John Kane" <jt-kane@comcast.net> wrote in message > news:ukWzZQkrDHA.3744@TK2MSFTNGP11.phx.gbl... > > Rogue, > > The first place to look for info on Change Tracking (other than this > > newsgroup) is SQL Server Books Online (BOL). You should review the > following > > titles: "Full-text Search" (Accessing and Changing Relational Data), > > "Full-Text Search Recommendations", "Maintaining Full-Text Indexes" > > (Creating and Maintaining Databases). sp_fulltext_table, > sp_fulltext_column > > and sp_help_fulltext_catalogs. Note, you do not have to run a Full > > Population prior to enabling CT with UIiB, as it will automatically > schedule > > a Full Population for an un-populated FT Catalog or an Incremental > > Population for a populated FT Catalog (assuming that a timestamp is in the > > FT-enabled table, otherwise a Full Population is executed.). > > > > Also, it implement Change Tracking with "Update Index in Background" via > > pure T-SQL use: > > > > EXEC sp_fulltext_table '<FT_table>', 'Start_change_tracking' > > EXEC sp_fulltext_table '<FT_table>', 'Start_background_updateindex' > > go > > > > Regards, > > John > > > > > > > > "Rogue Petunia" <roguepetunia@NOSPAMnyc.rr.com> wrote in message > > news:ehfpnJirDHA.640@tk2msftngp13.phx.gbl... > > > Hello, > > > I'm working on setting up full text search. What I want is to do > > background > > > updating with Change Tracking. I have a timestamp column on my table. > > > > > > I have filled the index once like this: > > > exec sp_fulltext_table @tabname = 'Copy', @action = 'start_full' > > > > > > How do I set it up to do background updating with Change Tracking now > that > > > 'start_full' has been peformed? > > > > > > Thanks a lot, > > > Rogue Petunia > > > > > > > > > > > > > > >
Thanks. that syntax was what I was looking for. Works perfectly now.
[quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message news:ukWzZQkrDHA.3744@TK2MSFTNGP11.phx.gbl... > Rogue, > The first place to look for info on Change Tracking (other than this > newsgroup) is SQL Server Books Online (BOL). You should review the following > titles: "Full-text Search" (Accessing and Changing Relational Data), > "Full-Text Search Recommendations", "Maintaining Full-Text Indexes" > (Creating and Maintaining Databases). sp_fulltext_table, sp_fulltext_column > and sp_help_fulltext_catalogs. Note, you do not have to run a Full > Population prior to enabling CT with UIiB, as it will automatically schedule > a Full Population for an un-populated FT Catalog or an Incremental > Population for a populated FT Catalog (assuming that a timestamp is in the > FT-enabled table, otherwise a Full Population is executed.). > > Also, it implement Change Tracking with "Update Index in Background" via > pure T-SQL use: > > EXEC sp_fulltext_table '<FT_table>', 'Start_change_tracking' > EXEC sp_fulltext_table '<FT_table>', 'Start_background_updateindex' > go > > Regards, > John > > > > "Rogue Petunia" <roguepetunia@NOSPAMnyc.rr.com> wrote in message > news:ehfpnJirDHA.640@tk2msftngp13.phx.gbl... > > Hello, > > I'm working on setting up full text search. What I want is to do > background > > updating with Change Tracking. I have a timestamp column on my table. > > > > I have filled the index once like this: > > exec sp_fulltext_table @tabname = 'Copy', @action = 'start_full' > > > > How do I set it up to do background updating with Change Tracking now that > > 'start_full' has been peformed? > > > > Thanks a lot, > > Rogue Petunia > > > > > > > >
John,
Another question, please. How can someone look at a Full Text Catalogue and find out what index maintanence has been set up for it? I know CT with UliB is set up on this one because I just did it, but how can another developer find out this info? Thanks, Rogue Petunia [quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message news:ukWzZQkrDHA.3744@TK2MSFTNGP11.phx.gbl... > Rogue, > The first place to look for info on Change Tracking (other than this > newsgroup) is SQL Server Books Online (BOL). You should review the following > titles: "Full-text Search" (Accessing and Changing Relational Data), > "Full-Text Search Recommendations", "Maintaining Full-Text Indexes" > (Creating and Maintaining Databases). sp_fulltext_table, sp_fulltext_column > and sp_help_fulltext_catalogs. Note, you do not have to run a Full > Population prior to enabling CT with UIiB, as it will automatically schedule > a Full Population for an un-populated FT Catalog or an Incremental > Population for a populated FT Catalog (assuming that a timestamp is in the > FT-enabled table, otherwise a Full Population is executed.). > > Also, it implement Change Tracking with "Update Index in Background" via > pure T-SQL use: > > EXEC sp_fulltext_table '<FT_table>', 'Start_change_tracking' > EXEC sp_fulltext_table '<FT_table>', 'Start_background_updateindex' > go > > Regards, > John > > > > "Rogue Petunia" <roguepetunia@NOSPAMnyc.rr.com> wrote in message > news:ehfpnJirDHA.640@tk2msftngp13.phx.gbl... > > Hello, > > I'm working on setting up full text search. What I want is to do > background > > updating with Change Tracking. I have a timestamp column on my table. > > > > I have filled the index once like this: > > exec sp_fulltext_table @tabname = 'Copy', @action = 'start_full' > > > > How do I set it up to do background updating with Change Tracking now that > > 'start_full' has been peformed? > > > > Thanks a lot, > > Rogue Petunia > > > > > > > >
Rogue,
Right, you are! Since this is being done in the Enterprise Manager, you can run Profiler while you click on the Full-Text Indexing tab for your FT-enable table and see what it is doing. For example and summarizing the actual SQL statements, for a table in the northwind database: use northwind go select ObjectProperty(object_id(N'[dbo].[Employees]'), N'TableHasActiveFulltextIndex') select objectproperty(object_id(N'[dbo].[Employees]'), N'TableFulltextChangeTrackingOn') select objectproperty(object_id(N'[dbo].[Employees]'), N'TableFullTextBackgroundUpdateIndexOn') select objectproperty(object_id(N'[dbo].[Employees]'), N'TableFullTextPopulateStatus') go You can then modify the above sql to generalize this to be used for any able table and put it in a user stored proc for your use! Thanks, as this is a good feature request! John [quoted text, click to view] "Rogue Petunia" <roguepetunia@NOSPAMnyc.rr.com> wrote in message news:#gLnqCsrDHA.1088@tk2msftngp13.phx.gbl... > SELECT fulltextcatalogproperty('Cat_Name', 'PopulateStatus') > > returns 1 of the 10 states below. If it's not in the middle of doing > something, building index, shuttting down, etc., 0 for Idle is returned. > > 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 > > > It doesn't tell what kind of index maintanence has been set up on the > catalogue. The only way I've been able to see that is through Enterprise > Manager. Choose a table that is in the index, select properties, choose > Full-Text Indexing tab. There I can see the following: > Full-text change-tracking: Enabled > Full-text update-index: Enabled > > This does gives me what I'm looking for. Seems there would be an sp for > that, though. > > Thanks, > Rogue > > "John Kane" <jt-kane@comcast.net> wrote in message > news:#oDGQRrrDHA.1088@tk2msftngp13.phx.gbl... > > Rogue, > > Sure... I think you're looking for the FTS metadata functions and help sp > as > > this will give you the status or type of FT Indexing maintenance you are > > looking for... > > > > sp_help_fulltext_catalogs > > -- see STATUS column for 9 = Change tracking > > > > SELECT fulltextcatalogproperty('PubInfoFTC', 'PopulateStatus') > > -- returns: 9 for Change tracking > > > > Regards, > > John > > > > > > > > "Rogue Petunia" <roguepetunia@NOSPAMnyc.rr.com> wrote in message > > news:uBL8XyqrDHA.1888@TK2MSFTNGP10.phx.gbl... > > > John, > > > Another question, please. How can someone look at a Full Text Catalogue > > and > > > find out what index maintanence has been set up for it? I know CT with > > UliB > > > is set up on this one because I just did it, but how can another > developer > > > find out this info? > > > > > > Thanks, > > > Rogue Petunia > > > > > > "John Kane" <jt-kane@comcast.net> wrote in message > > > news:ukWzZQkrDHA.3744@TK2MSFTNGP11.phx.gbl... > > > > Rogue, > > > > The first place to look for info on Change Tracking (other than this > > > > newsgroup) is SQL Server Books Online (BOL). You should review the > > > following > > > > titles: "Full-text Search" (Accessing and Changing Relational Data), > > > > "Full-Text Search Recommendations", "Maintaining Full-Text Indexes" > > > > (Creating and Maintaining Databases). sp_fulltext_table, > > > sp_fulltext_column > > > > and sp_help_fulltext_catalogs. Note, you do not have to run a Full > > > > Population prior to enabling CT with UIiB, as it will automatically > > > schedule > > > > a Full Population for an un-populated FT Catalog or an Incremental > > > > Population for a populated FT Catalog (assuming that a timestamp is in > > the > > > > FT-enabled table, otherwise a Full Population is executed.). > > > > > > > > Also, it implement Change Tracking with "Update Index in Background" > via > > > > pure T-SQL use: > > > > > > > > EXEC sp_fulltext_table '<FT_table>', 'Start_change_tracking' > > > > EXEC sp_fulltext_table '<FT_table>', 'Start_background_updateindex' > > > > go > > > > > > > > Regards, > > > > John > > > > > > > > > > > > > > > > "Rogue Petunia" <roguepetunia@NOSPAMnyc.rr.com> wrote in message > > > > news:ehfpnJirDHA.640@tk2msftngp13.phx.gbl... > > > > > Hello, > > > > > I'm working on setting up full text search. What I want is to do > > > > background > > > > > updating with Change Tracking. I have a timestamp column on my > table. > > > > > > > > > > I have filled the index once like this: > > > > > exec sp_fulltext_table @tabname = 'Copy', @action = 'start_full' > > > > > > > > > > How do I set it up to do background updating with Change Tracking > now > > > that > > > > > 'start_full' has been peformed? > > > > > > > > > > Thanks a lot, > > > > > Rogue Petunia > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
SELECT fulltextcatalogproperty('Cat_Name', 'PopulateStatus')
returns 1 of the 10 states below. If it's not in the middle of doing something, building index, shuttting down, etc., 0 for Idle is returned. 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 It doesn't tell what kind of index maintanence has been set up on the catalogue. The only way I've been able to see that is through Enterprise Manager. Choose a table that is in the index, select properties, choose Full-Text Indexing tab. There I can see the following: Full-text change-tracking: Enabled Full-text update-index: Enabled This does gives me what I'm looking for. Seems there would be an sp for that, though. Thanks, Rogue [quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message news:#oDGQRrrDHA.1088@tk2msftngp13.phx.gbl... > Rogue, > Sure... I think you're looking for the FTS metadata functions and help sp as > this will give you the status or type of FT Indexing maintenance you are > looking for... > > sp_help_fulltext_catalogs > -- see STATUS column for 9 = Change tracking > > SELECT fulltextcatalogproperty('PubInfoFTC', 'PopulateStatus') > -- returns: 9 for Change tracking > > Regards, > John > > > > "Rogue Petunia" <roguepetunia@NOSPAMnyc.rr.com> wrote in message > news:uBL8XyqrDHA.1888@TK2MSFTNGP10.phx.gbl... > > John, > > Another question, please. How can someone look at a Full Text Catalogue > and > > find out what index maintanence has been set up for it? I know CT with > UliB > > is set up on this one because I just did it, but how can another developer > > find out this info? > > > > Thanks, > > Rogue Petunia > > > > "John Kane" <jt-kane@comcast.net> wrote in message > > news:ukWzZQkrDHA.3744@TK2MSFTNGP11.phx.gbl... > > > Rogue, > > > The first place to look for info on Change Tracking (other than this > > > newsgroup) is SQL Server Books Online (BOL). You should review the > > following > > > titles: "Full-text Search" (Accessing and Changing Relational Data), > > > "Full-Text Search Recommendations", "Maintaining Full-Text Indexes" > > > (Creating and Maintaining Databases). sp_fulltext_table, > > sp_fulltext_column > > > and sp_help_fulltext_catalogs. Note, you do not have to run a Full > > > Population prior to enabling CT with UIiB, as it will automatically > > schedule > > > a Full Population for an un-populated FT Catalog or an Incremental > > > Population for a populated FT Catalog (assuming that a timestamp is in > the > > > FT-enabled table, otherwise a Full Population is executed.). > > > > > > Also, it implement Change Tracking with "Update Index in Background" via > > > pure T-SQL use: > > > > > > EXEC sp_fulltext_table '<FT_table>', 'Start_change_tracking' > > > EXEC sp_fulltext_table '<FT_table>', 'Start_background_updateindex' > > > go > > > > > > Regards, > > > John > > > > > > > > > > > > "Rogue Petunia" <roguepetunia@NOSPAMnyc.rr.com> wrote in message > > > news:ehfpnJirDHA.640@tk2msftngp13.phx.gbl... > > > > Hello, > > > > I'm working on setting up full text search. What I want is to do > > > background > > > > updating with Change Tracking. I have a timestamp column on my table. > > > > > > > > I have filled the index once like this: > > > > exec sp_fulltext_table @tabname = 'Copy', @action = 'start_full' > > > > > > > > How do I set it up to do background updating with Change Tracking now > > that > > > > 'start_full' has been peformed? > > > > > > > > Thanks a lot, > > > > Rogue Petunia > > > > > > > > > > > > > > > > > > > > > > > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |