|
|
You're in the
sql server full text search
group:Full Text Index Not Triggering on Row Insert.
sql server full text search:
I have a database table that I would like to integrate a full text index on and have the table automatically rebuild the index on each insert/update/delete. I think I have a good idea on the methodology from reading through the posts, but I cannot get it to work on an automatic basis, If I incrementally rebuild the index manually through SQL enterprise then everything works great. My table looks like this: EntryID int, EntryDescription char(30), /* This is the field that is full text indexed */ TimeUpdated timestamp At this point, I should point out that I'm dealing with a database built awhile back that has many applications tied into it. Therefore, I was able to add the timestamp field without any affect to existing applications but I am unable to change the Description field to another data type without adverse effects. I only point this out as I am not sure if the fact that it's not a text field has anything to do with my problem. So, then under SQL Enterprise I have selected, under Full-Text Index: * Change tracking * Update Index in Background My application then adds a new entry to this table using a standard INSERT INTO stored procedure, and I can verify the entry was successfully added by a simple SELECT query. However, any query that looks for the new value using a text-indexed based query does not return the new record until I manually rebuild the index. As mentioned, I think I got all the steps and also realize that I could use the method of scheduling an incremental build every X minutes. However, I'd like to keep the application as "real time" as possible so any help and/or thoughts is very much appreciated. Thanks
Jason,
Can I assume you're using SQL Server 2000? "Change tracking" and "Update Index in Background" is what you're looking for and as I just recently developed a test script for another thread, it worked as expected with SQL FTS queries returning expected results after I did an INSERT. I can email you the script for you to test if you want... Could you post both the INSERT INTO stored proc and your "query that looks for the new value using a text-indexed based query does not return the new record"? This should of worked for you as well as it did for me, it's just that we need to find why it failed to work successfully for you... Regards, John [quoted text, click to view] "arby" <goosh@yahoo.com> wrote in message news:372340af.0401292139.4b120fdc@posting.google.com... > Hi There. > > I have a database table that I would like to integrate a full text > index on and have the table automatically rebuild the index on each > insert/update/delete. I think I have a good idea on the methodology > from reading through the posts, but I cannot get it to work on an > automatic basis, If I incrementally rebuild the index manually through > SQL enterprise then everything works great. > > My table looks like this: > > EntryID int, > EntryDescription char(30), /* This is the field that is full text > indexed */ > TimeUpdated timestamp > > At this point, I should point out that I'm dealing with a database > built awhile back that has many applications tied into it. Therefore, > I was able to add the timestamp field without any affect to existing > applications but I am unable to change the Description field to > another data type without adverse effects. I only point this out as I > am not sure if the fact that it's not a text field has anything to do > with my problem. > > So, then under SQL Enterprise I have selected, under Full-Text Index: > * Change tracking > * Update Index in Background > > My application then adds a new entry to this table using a standard > INSERT INTO stored procedure, and I can verify the entry was > successfully added by a simple SELECT query. However, any query that > looks for the new value using a text-indexed based query does not > return the new record until I manually rebuild the index. > > As mentioned, I think I got all the steps and also realize that I > could use the method of scheduling an incremental build every X > minutes. However, I'd like to keep the application as "real time" as > possible so any help and/or thoughts is very much appreciated. > > Thanks > Jason
Hi John,
I'm pretty sure it's 2000, how can I verify? I see XML support though which I thought was new to 2000. Anyways, the piece of the stored procedure that does the insert is: " If @Description <> '' INSERT INTO LogDescriptions (LogID, Description) VALUES (@ID, @Description) " Then, the search procedure is: CREATE PROCEDURE dbo.LogsSearch @TeamID int = 0, @PlayerID int = 0 , @CategoryID int = 0, @SubCategoryID int = 0, @Keywords varchar(20) = '<blank>' AS If @Keywords = '' SELECT @Keywords = '<blank>' SELECT M.MediaNum, M.TimeIn, M.Duration, V.Abbreviation As Visitors, H.Abbreviation as Home, Q.Description As Quality, D.Description FROM LogMain M JOIN Teams V ON M.VisitorsID = V.ID JOIN Teams H ON M.HomeID = H.ID JOIN zCategory C ON M.CategoryID = C.ID JOIN zSubCategory SC ON M.SubCategoryID = SC.ID JOIN zQuality Q ON M.QualityID = Q.ID LEFT OUTER JOIN LogDescriptions D ON D.LogID = M.ID /* Build Dynamic WHERE Clauses based upon input */ WHERE ((@Keywords <> '<blank>' AND CONTAINS (D.Description, @KeyWords) ) OR @Keywords = '<blank>') Thanks Jason [quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message news:<ulsbBPw5DHA.3548@TK2MSFTNGP11.phx.gbl>...
> Jason, > Can I assume you're using SQL Server 2000? "Change tracking" and "Update > Index in Background" is what you're looking for and as I just recently > developed a test script for another thread, it worked as expected with SQL > FTS queries returning expected results after I did an INSERT. I can email > you the script for you to test if you want... > > Could you post both the INSERT INTO stored proc and your "query that looks > for the new value using a text-indexed based query does not return the new > record"? This should of worked for you as well as it did for me, it's just > that we need to find why it failed to work successfully for you... > > Regards, > John > > > > "arby" <goosh@yahoo.com> wrote in message > news:372340af.0401292139.4b120fdc@posting.google.com... > > Hi There. > > > > I have a database table that I would like to integrate a full text > > index on and have the table automatically rebuild the index on each > > insert/update/delete. I think I have a good idea on the methodology > > from reading through the posts, but I cannot get it to work on an > > automatic basis, If I incrementally rebuild the index manually through > > SQL enterprise then everything works great. > > > > My table looks like this: > > > > EntryID int, > > EntryDescription char(30), /* This is the field that is full text > > indexed */ > > TimeUpdated timestamp > > > > At this point, I should point out that I'm dealing with a database > > built awhile back that has many applications tied into it. Therefore, > > I was able to add the timestamp field without any affect to existing > > applications but I am unable to change the Description field to > > another data type without adverse effects. I only point this out as I > > am not sure if the fact that it's not a text field has anything to do > > with my problem. > > > > So, then under SQL Enterprise I have selected, under Full-Text Index: > > * Change tracking > > * Update Index in Background > > > > My application then adds a new entry to this table using a standard > > INSERT INTO stored procedure, and I can verify the entry was > > successfully added by a simple SELECT query. However, any query that > > looks for the new value using a text-indexed based query does not > > return the new record until I manually rebuild the index. > > > > As mentioned, I think I got all the steps and also realize that I > > could use the method of scheduling an incremental build every X > > minutes. However, I'd like to keep the application as "real time" as > > possible so any help and/or thoughts is very much appreciated. > > > > Thanks
Hi Jason,
You can use SELECT @@version -- to verify the exact SQL Server version, including Service Pack (SP) level and OS Platform version as well. Both XML and "Change Tracking" are new with SQL Server 2000... The INSERT statement is stright forward, although, an Insert is only done if the @Description value is not null, and I'm assuming that is true. While the LogsSearch proc is a bit more complex. Could you retest using a simple INSERT statement, and a simple CONTAINS statement, using a search word from the INSERT statement, for example: INSERT INTO FTSAccent values('Kahlúa HalfPipe jam', 'cafè', 'cafe', NULL) .... select TextCol from FTSAccent where contains(TextCol,'"jam"') Could you also confirm that "Change Tracking" and "Update Index in Background" are turned on with your table LogDescriptions? Could you post the results of the following SQL code? use <your_database_name> go exec sp_help_fulltext_catalogs select objectproperty(object_id(N'LogDescriptions'),N'TableFulltextChangeTrackingOn ') select objectproperty(object_id(N'LogDescriptions'),N'TableFullTextBackgroundUpdate IndexOn') select objectproperty(object_id(N'LogDescriptions'),N'TableFullTextPopulateStatus') go Thanks, John [quoted text, click to view] "arby" <goosh@yahoo.com> wrote in message news:372340af.0401301006.33cea04a@posting.google.com... > Hi John, > > I'm pretty sure it's 2000, how can I verify? I see XML support though > which I thought was new to 2000. > > Anyways, the piece of the stored procedure that does the insert is: > > " > If @Description <> '' > > INSERT INTO LogDescriptions > (LogID, Description) > VALUES > (@ID, @Description) > " > > Then, the search procedure is: > > CREATE PROCEDURE dbo.LogsSearch > > @TeamID int = 0, > @PlayerID int = 0 , > @CategoryID int = 0, > @SubCategoryID int = 0, > @Keywords varchar(20) = '<blank>' > AS > > If @Keywords = '' > SELECT @Keywords = '<blank>' > > SELECT > M.MediaNum, > M.TimeIn, > M.Duration, > V.Abbreviation As Visitors, > H.Abbreviation as Home, > Q.Description As Quality, > D.Description > FROM LogMain M > JOIN Teams V ON M.VisitorsID = V.ID > JOIN Teams H ON M.HomeID = H.ID > JOIN zCategory C ON M.CategoryID = C.ID > JOIN zSubCategory SC ON M.SubCategoryID = SC.ID > JOIN zQuality Q ON M.QualityID = Q.ID > LEFT OUTER JOIN LogDescriptions D ON D.LogID = M.ID > > /* Build Dynamic WHERE Clauses based upon input */ > WHERE ((@Keywords <> '<blank>' AND CONTAINS (D.Description, > @KeyWords) ) OR @Keywords = '<blank>') > > > Thanks > Jason > > > > > "John Kane" <jt-kane@comcast.net> wrote in message news:<ulsbBPw5DHA.3548@TK2MSFTNGP11.phx.gbl>... > > Jason, > > Can I assume you're using SQL Server 2000? "Change tracking" and "Update > > Index in Background" is what you're looking for and as I just recently > > developed a test script for another thread, it worked as expected with SQL > > FTS queries returning expected results after I did an INSERT. I can > > you the script for you to test if you want... > > > > Could you post both the INSERT INTO stored proc and your "query that looks > > for the new value using a text-indexed based query does not return the new > > record"? This should of worked for you as well as it did for me, it's just > > that we need to find why it failed to work successfully for you... > > > > Regards, > > John > > > > > > > > "arby" <goosh@yahoo.com> wrote in message > > news:372340af.0401292139.4b120fdc@posting.google.com... > > > Hi There. > > > > > > I have a database table that I would like to integrate a full text > > > index on and have the table automatically rebuild the index on each > > > insert/update/delete. I think I have a good idea on the methodology > > > from reading through the posts, but I cannot get it to work on an > > > automatic basis, If I incrementally rebuild the index manually through > > > SQL enterprise then everything works great. > > > > > > My table looks like this: > > > > > > EntryID int, > > > EntryDescription char(30), /* This is the field that is full text > > > indexed */ > > > TimeUpdated timestamp > > > > > > At this point, I should point out that I'm dealing with a database > > > built awhile back that has many applications tied into it. Therefore, > > > I was able to add the timestamp field without any affect to existing > > > applications but I am unable to change the Description field to > > > another data type without adverse effects. I only point this out as I > > > am not sure if the fact that it's not a text field has anything to do > > > with my problem. > > > > > > So, then under SQL Enterprise I have selected, under Full-Text Index: > > > * Change tracking > > > * Update Index in Background > > > > > > My application then adds a new entry to this table using a standard > > > INSERT INTO stored procedure, and I can verify the entry was > > > successfully added by a simple SELECT query. However, any query that > > > looks for the new value using a text-indexed based query does not > > > return the new record until I manually rebuild the index. > > > > > > As mentioned, I think I got all the steps and also realize that I > > > could use the method of scheduling an incremental build every X > > > minutes. However, I'd like to keep the application as "real time" as > > > possible so any help and/or thoughts is very much appreciated. > > > > > > Thanks > > > Jason
Hi John,
Well, I think I figured it out. I think it was working all along but just taking a bit longer then expected (about 5 seconds). I expected near real-time results so immediately upon my insert I would requery and it would not show up... but after some more testing I'm finding it does show up but not as quick as I would have anticipated. Thanks for all your help though. Jason [quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message news:<#8oUJF25DHA.2488@TK2MSFTNGP09.phx.gbl>...
> Hi Jason, > You can use SELECT @@version -- to verify the exact SQL Server version, > including Service Pack (SP) level and OS Platform version as well. Both XML > and "Change Tracking" are new with SQL Server 2000... > > The INSERT statement is stright forward, although, an Insert is only done if > the @Description value is not null, and I'm assuming that is true. While the > LogsSearch proc is a bit more complex. Could you retest using a simple > INSERT statement, and a simple CONTAINS statement, using a search word from > the INSERT statement, for example: > > INSERT INTO FTSAccent values('Kahlúa HalfPipe jam', 'cafè', 'cafe', NULL) > ... > select TextCol from FTSAccent where contains(TextCol,'"jam"') > > Could you also confirm that "Change Tracking" and "Update Index in > Background" are turned on with your table LogDescriptions? Could you post > the results of the following SQL code? > > use <your_database_name> > go > exec sp_help_fulltext_catalogs > select > objectproperty(object_id(N'LogDescriptions'),N'TableFulltextChangeTrackingOn > ') > select > objectproperty(object_id(N'LogDescriptions'),N'TableFullTextBackgroundUpdate > IndexOn') > select > objectproperty(object_id(N'LogDescriptions'),N'TableFullTextPopulateStatus') > go > > Thanks, > John > > > "arby" <goosh@yahoo.com> wrote in message > news:372340af.0401301006.33cea04a@posting.google.com... > > Hi John, > > > > I'm pretty sure it's 2000, how can I verify? I see XML support though > > which I thought was new to 2000. > > > > Anyways, the piece of the stored procedure that does the insert is: > > > > " > > If @Description <> '' > > > > INSERT INTO LogDescriptions > > (LogID, Description) > > VALUES > > (@ID, @Description) > > " > > > > Then, the search procedure is: > > > > CREATE PROCEDURE dbo.LogsSearch > > > > @TeamID int = 0, > > @PlayerID int = 0 , > > @CategoryID int = 0, > > @SubCategoryID int = 0, > > @Keywords varchar(20) = '<blank>' > > AS > > > > If @Keywords = '' > > SELECT @Keywords = '<blank>' > > > > SELECT > > M.MediaNum, > > M.TimeIn, > > M.Duration, > > V.Abbreviation As Visitors, > > H.Abbreviation as Home, > > Q.Description As Quality, > > D.Description > > FROM LogMain M > > JOIN Teams V ON M.VisitorsID = V.ID > > JOIN Teams H ON M.HomeID = H.ID > > JOIN zCategory C ON M.CategoryID = C.ID > > JOIN zSubCategory SC ON M.SubCategoryID = SC.ID > > JOIN zQuality Q ON M.QualityID = Q.ID > > LEFT OUTER JOIN LogDescriptions D ON D.LogID = M.ID > > > > /* Build Dynamic WHERE Clauses based upon input */ > > WHERE ((@Keywords <> '<blank>' AND CONTAINS (D.Description, > > @KeyWords) ) OR @Keywords = '<blank>') > > > > > > Thanks > > Jason > > > > > > > > > > "John Kane" <jt-kane@comcast.net> wrote in message > news:<ulsbBPw5DHA.3548@TK2MSFTNGP11.phx.gbl>... > > > Jason, > > > Can I assume you're using SQL Server 2000? "Change tracking" and "Update > > > Index in Background" is what you're looking for and as I just recently > > > developed a test script for another thread, it worked as expected with > SQL > > > FTS queries returning expected results after I did an INSERT. I can > > > you the script for you to test if you want... > > > > > > Could you post both the INSERT INTO stored proc and your "query that > looks > > > for the new value using a text-indexed based query does not return the > new > > > record"? This should of worked for you as well as it did for me, it's > just > > > that we need to find why it failed to work successfully for you... > > > > > > Regards, > > > John > > > > > > > > > > > > "arby" <goosh@yahoo.com> wrote in message > > > news:372340af.0401292139.4b120fdc@posting.google.com... > > > > Hi There. > > > > > > > > I have a database table that I would like to integrate a full text > > > > index on and have the table automatically rebuild the index on each > > > > insert/update/delete. I think I have a good idea on the methodology > > > > from reading through the posts, but I cannot get it to work on an > > > > automatic basis, If I incrementally rebuild the index manually through > > > > SQL enterprise then everything works great. > > > > > > > > My table looks like this: > > > > > > > > EntryID int, > > > > EntryDescription char(30), /* This is the field that is full text > > > > indexed */ > > > > TimeUpdated timestamp > > > > > > > > At this point, I should point out that I'm dealing with a database > > > > built awhile back that has many applications tied into it. Therefore, > > > > I was able to add the timestamp field without any affect to existing > > > > applications but I am unable to change the Description field to > > > > another data type without adverse effects. I only point this out as I > > > > am not sure if the fact that it's not a text field has anything to do > > > > with my problem. > > > > > > > > So, then under SQL Enterprise I have selected, under Full-Text Index: > > > > * Change tracking > > > > * Update Index in Background > > > > > > > > My application then adds a new entry to this table using a standard > > > > INSERT INTO stored procedure, and I can verify the entry was > > > > successfully added by a simple SELECT query. However, any query that > > > > looks for the new value using a text-indexed based query does not > > > > return the new record until I manually rebuild the index. > > > > > > > > As mentioned, I think I got all the steps and also realize that I > > > > could use the method of scheduling an incremental build every X > > > > minutes. However, I'd like to keep the application as "real time" as > > > > possible so any help and/or thoughts is very much appreciated. > > > > > > > > Thanks
You're welcome, Jason,
There a built-in delay of approx. 1 second with "Change Tracking" and "Update Index in Background" before the FT Catalog is updated, so depending upon the number of inserts, you're most likely seeing this delay... Regards, John [quoted text, click to view] "arby" <goosh@yahoo.com> wrote in message asnews:372340af.0402031607.1b9590b2@posting.google.com... > Hi John, > > Well, I think I figured it out. I think it was working all along but > just taking a bit longer then expected (about 5 seconds). I expected > near real-time results so immediately upon my insert I would requery > and it would not show up... but after some more testing I'm finding it > does show up but not as quick as I would have anticipated. > > Thanks for all your help though. > > Jason > > > "John Kane" <jt-kane@comcast.net> wrote in message news:<#8oUJF25DHA.2488@TK2MSFTNGP09.phx.gbl>... > > Hi Jason, > > You can use SELECT @@version -- to verify the exact SQL Server version, > > including Service Pack (SP) level and OS Platform version as well. Both XML > > and "Change Tracking" are new with SQL Server 2000... > > > > The INSERT statement is stright forward, although, an Insert is only done if > > the @Description value is not null, and I'm assuming that is true. While the > > LogsSearch proc is a bit more complex. Could you retest using a simple > > INSERT statement, and a simple CONTAINS statement, using a search word from > > the INSERT statement, for example: > > > > INSERT INTO FTSAccent values('Kahlúa HalfPipe jam', 'cafè', 'cafe', NULL) > > ... > > select TextCol from FTSAccent where contains(TextCol,'"jam"') > > > > Could you also confirm that "Change Tracking" and "Update Index in > > Background" are turned on with your table LogDescriptions? Could you post > > the results of the following SQL code? > > > > use <your_database_name> > > go > > exec sp_help_fulltext_catalogs > > select > > objectproperty(object_id(N'LogDescriptions'),N'TableFulltextChangeTrackingOn > > ') > > select > > objectproperty(object_id(N'LogDescriptions'),N'TableFullTextBackgroundUpdate > > IndexOn') > > select > > objectproperty(object_id(N'LogDescriptions'),N'TableFullTextPopulateStatus') > > go > > > > Thanks, > > John > > > > > > "arby" <goosh@yahoo.com> wrote in message > > news:372340af.0401301006.33cea04a@posting.google.com... > > > Hi John, > > > > > > I'm pretty sure it's 2000, how can I verify? I see XML support though > > > which I thought was new to 2000. > > > > > > Anyways, the piece of the stored procedure that does the insert is: > > > > > > " > > > If @Description <> '' > > > > > > INSERT INTO LogDescriptions > > > (LogID, Description) > > > VALUES > > > (@ID, @Description) > > > " > > > > > > Then, the search procedure is: > > > > > > CREATE PROCEDURE dbo.LogsSearch > > > > > > @TeamID int = 0, > > > @PlayerID int = 0 , > > > @CategoryID int = 0, > > > @SubCategoryID int = 0, > > > @Keywords varchar(20) = '<blank>' > > > AS > > > > > > If @Keywords = '' > > > SELECT @Keywords = '<blank>' > > > > > > SELECT > > > M.MediaNum, > > > M.TimeIn, > > > M.Duration, > > > V.Abbreviation As Visitors, > > > H.Abbreviation as Home, > > > Q.Description As Quality, > > > D.Description > > > FROM LogMain M > > > JOIN Teams V ON M.VisitorsID = V.ID > > > JOIN Teams H ON M.HomeID = H.ID > > > JOIN zCategory C ON M.CategoryID = C.ID > > > JOIN zSubCategory SC ON M.SubCategoryID = SC.ID > > > JOIN zQuality Q ON M.QualityID = Q.ID > > > LEFT OUTER JOIN LogDescriptions D ON D.LogID = M.ID > > > > > > /* Build Dynamic WHERE Clauses based upon input */ > > > WHERE ((@Keywords <> '<blank>' AND CONTAINS (D.Description, > > > @KeyWords) ) OR @Keywords = '<blank>') > > > > > > > > > Thanks > > > Jason > > > > > > > > > > > > > > > "John Kane" <jt-kane@comcast.net> wrote in message > > news:<ulsbBPw5DHA.3548@TK2MSFTNGP11.phx.gbl>... > > > > Jason, > > > > Can I assume you're using SQL Server 2000? "Change tracking" and "Update > > > > Index in Background" is what you're looking for and as I just recently > > > > developed a test script for another thread, it worked as expected with > > SQL > > > > FTS queries returning expected results after I did an INSERT. I can > > > > you the script for you to test if you want... > > > > > > > > Could you post both the INSERT INTO stored proc and your "query that > > looks > > > > for the new value using a text-indexed based query does not return the > > new > > > > record"? This should of worked for you as well as it did for me, it's > > just > > > > that we need to find why it failed to work successfully for you... > > > > > > > > Regards, > > > > John > > > > > > > > > > > > > > > > "arby" <goosh@yahoo.com> wrote in message > > > > news:372340af.0401292139.4b120fdc@posting.google.com... > > > > > Hi There. > > > > > > > > > > I have a database table that I would like to integrate a full text > > > > > index on and have the table automatically rebuild the index on each > > > > > insert/update/delete. I think I have a good idea on the methodology > > > > > from reading through the posts, but I cannot get it to work on an > > > > > automatic basis, If I incrementally rebuild the index manually through > > > > > SQL enterprise then everything works great. > > > > > > > > > > My table looks like this: > > > > > > > > > > EntryID int, > > > > > EntryDescription char(30), /* This is the field that is full text > > > > > indexed */ > > > > > TimeUpdated timestamp > > > > > > > > > > At this point, I should point out that I'm dealing with a database > > > > > built awhile back that has many applications tied into it. Therefore, > > > > > I was able to add the timestamp field without any affect to existing > > > > > applications but I am unable to change the Description field to > > > > > another data type without adverse effects. I only point this out as I > > > > > am not sure if the fact that it's not a text field has anything to do > > > > > with my problem. > > > > > > > > > > So, then under SQL Enterprise I have selected, under Full-Text Index: > > > > > * Change tracking > > > > > * Update Index in Background > > > > > > > > > > My application then adds a new entry to this table using a standard > > > > > INSERT INTO stored procedure, and I can verify the entry was > > > > > successfully added by a simple SELECT query. However, any query that > > > > > looks for the new value using a text-indexed based query does not > > > > > return the new record until I manually rebuild the index. > > > > > > > > > > As mentioned, I think I got all the steps and also realize that I > > > > > could use the method of scheduling an incremental build every X > > > > > minutes. However, I'd like to keep the application as "real time"
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 |