Automatically re-indexing

Is there a way to tell the system to re-index a table after a change - any
change is made to the table?
Is there a way to tell the system to re-index using a stored procedure?
Thanks,
Tom.
Tom,
Yes. That is assuming you are using SQL Server 2000, you can use "Change
Tracking" and "Update Index in Background" and get near real-time updates of
the FT Catalogs when a table's FT-enabled column changes. SQL Server 2000
BOL titles "Full-Text Search Recommendations" and "Maintaining Full-Text
Indexes" have more info on these options. If you're using SQL Server 7.0,
then you must schedule and run an Incremental Population (substitute
start_incremental for start_full in the below sql code).
As for a stored proc that can do this, try:
use pubs
go
if object_id('sp_WrapFT_SProcs','P') IS NOT NULL
drop procedure sp_WrapFT_SProcs
GO
CREATE PROCEDURE sp_WrapFT_SProcs @.tablename varchar(100), @.activity
varchar(100)
AS
DECLARE @.SQLCMD varchar(255)
SELECT @.SQLCMD = "sp_fulltext_catalog @.tablename, @.activity"
EXEC (@.SQLCMD)
GO
-- Execute above:
EXEC sp_WrapFT_SProcs 'PubInfo', 'start_full'
GO
Regards,
John
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:10h0diu7prv0h39@.corp.supernews.com...
> Is there a way to tell the system to re-index a table after a change - any
> change is made to the table?
> Is there a way to tell the system to re-index using a stored procedure?
> Thanks,
> Tom.
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:#F671tceEHA.724@.TK2MSFTNGP10.phx.gbl...
> Tom,
> Yes. That is assuming you are using SQL Server 2000, you can use "Change
> Tracking" and "Update Index in Background" and get near real-time updates
of
> the FT Catalogs when a table's FT-enabled column changes. SQL Server 2000
> BOL titles "Full-Text Search Recommendations" and "Maintaining Full-Text
> Indexes" have more info on these options. If you're using SQL Server 7.0,
> then you must schedule and run an Incremental Population (substitute
> start_incremental for start_full in the below sql code).
Yes, I am using Sql Server 2000.
What I am doing is setting up a table with all my QA Docs (about 150 of
them).
I have an ASP.NET page that just goes through my Document folder and creates
the records as well as copies the documents into my image field.
What I am doing is trying to set up an easy way to update the records when
some of the documents have changed. Instead of trying to determine which
have been changed, I plan to just delete all the records and create all the
records again (only takes about 3 minutes). I just want to make sure that
index is also updated.
Thanks,
Tom.[vbcol=seagreen]
> As for a stored proc that can do this, try:
> use pubs
> go
> if object_id('sp_WrapFT_SProcs','P') IS NOT NULL
> drop procedure sp_WrapFT_SProcs
> GO
> CREATE PROCEDURE sp_WrapFT_SProcs @.tablename varchar(100), @.activity
> varchar(100)
> AS
> DECLARE @.SQLCMD varchar(255)
> SELECT @.SQLCMD = "sp_fulltext_catalog @.tablename, @.activity"
> EXEC (@.SQLCMD)
> GO
> -- Execute above:
> EXEC sp_WrapFT_SProcs 'PubInfo', 'start_full'
> GO
> Regards,
> John
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:10h0diu7prv0h39@.corp.supernews.com...
any
>
|||Tom,
There should be an easy way to determine when & what document has changed,
at a mim. save the doc length &/or mod date/time in your SQL table and then
only upload the changed documents. With CT & UIiB enabled, each time you
upload (or really update/insert) the document into your FT-enable image
column, CT & UIiB will automatically update the FT Catalog with the
new/modified documents.
Even if it takes only 3 minutes, deleting all the records and then
re-creating all the records again, seems a bit time-consuming (not very
scalable as you get more & more documents) and wasteful as all documents
would then need to be re-FT Indexed and that can take more time as you get
more & more documents, IMHO.
Regards,
John
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:10h0rrtl23mhi76@.corp.supernews.com...[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:#F671tceEHA.724@.TK2MSFTNGP10.phx.gbl...
updates[vbcol=seagreen]
> of
2000[vbcol=seagreen]
7.0,
> Yes, I am using Sql Server 2000.
> What I am doing is setting up a table with all my QA Docs (about 150 of
> them).
> I have an ASP.NET page that just goes through my Document folder and
creates
> the records as well as copies the documents into my image field.
> What I am doing is trying to set up an easy way to update the records when
> some of the documents have changed. Instead of trying to determine which
> have been changed, I plan to just delete all the records and create all
the[vbcol=seagreen]
> records again (only takes about 3 minutes). I just want to make sure that
> index is also updated.
> Thanks,
> Tom.
> any
procedure?
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:OnizJ8eeEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Tom,
> There should be an easy way to determine when & what document has changed,
> at a mim. save the doc length &/or mod date/time in your SQL table and
then
> only upload the changed documents. With CT & UIiB enabled, each time you
> upload (or really update/insert) the document into your FT-enable image
> column, CT & UIiB will automatically update the FT Catalog with the
> new/modified documents.
> Even if it takes only 3 minutes, deleting all the records and then
> re-creating all the records again, seems a bit time-consuming (not very
> scalable as you get more & more documents) and wasteful as all documents
> would then need to be re-FT Indexed and that can take more time as you get
> more & more documents, IMHO.
You're right.
And if I set CT & UIiB, I shouldn't have to check the length or time,
anyway.
Thanks,
Tom[vbcol=seagreen]
> Regards,
> John
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:10h0rrtl23mhi76@.corp.supernews.com...
"Change[vbcol=seagreen]
> updates
> 2000
Full-Text[vbcol=seagreen]
> 7.0,
> creates
when[vbcol=seagreen]
which[vbcol=seagreen]
> the
that[vbcol=seagreen]
change -
> procedure?
>