Programmatically Create a Full Text Population Schedule via TSQL

Hi All,

I have a SQL 2005 TSQL script which sets up my FT Catalog and Indexes, but I would like to also create a Population schedule for these indexes via the same TSQL Code, can anyone point me in the right direction so I do not have to use the GUI to do this.

Thank you Matt
wickedwAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BodestoneCommented:
If this is because you may have to deploy this across many databases then one quick way is to start using the SMSS GUI to create the schedue but rather than committing use the SQL option at the top.

This should give you the options to export the SQL that the GUI would use to do its stuff. You can then adapt that with variables etc. for later use.

I found that SQL option very handy for learnign bits of administrative SQL I might not otherwise thought to have learned just wandering round and experimenting since it is present for most operations.
0
wickedwAuthor Commented:
Thanks for reply bodestone, just tried to perform a create schedule action on a full text (via GUI) and hit the script button, unfortunately I cant get this to work, the only one it seems to work for is after a delete schedule action.

There is also no script button when creating it from scratch.    Thought this should be reasonably simple, but not sure now.

Any ideas anyone?
0
BodestoneCommented:
You could always run the profiler to capture what it happening in the back end. Get it running, clear everything and hit commit in SSMS and see what the profiler captures.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Anthony PerkinsCommented:
Can you post your current script?  This will tell me what version you are using:  The old sp_fulltext System Stored Procedures or the new CREATE, ALTER, and DROP FULLTEXT INDEX syntax.

Also, rather than create a Full Population you should consider flagging the Full-Text search for Change Tracking, otherwise you are going to have to schedule this periodically.
0
wickedwAuthor Commented:
Hi acperkins,

Here is an abbreviated version, and as you can see I want to keep change tracking off for now.  

Thanks.

-- DROP EXISTING
IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[t_category]'))
ALTER FULLTEXT INDEX ON [dbo].[t_category] DISABLE
IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[t_category]'))
DROP FULLTEXT INDEX ON [dbo].[t_category]

IF  EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'Catalog')
DROP FULLTEXT CATALOG [Catalog]

CREATE FULLTEXT CATALOG Catalog
      AS DEFAULT

CREATE FULLTEXT INDEX ON dbo.t_category
      ([name])
      KEY INDEX PK_t_category
      WITH
      CHANGE_TRACKING OFF

...

If I could set up a daily incremental, and a weekly full population via code I would be good to go :)
0
Anthony PerkinsCommented:
Is there any reason you do not want to use Change Tracking?  It seems to me a no-brainer and what we use, just curious as to your rationale.
0
wickedwAuthor Commented:
We import and update a lot of records in batches overnight, and can cause some locking issues in change tracking on.  We only refresh nightly, so a daily incremental  suits us fine.
0
Anthony PerkinsCommented:
The following will start a Full Population:
ALTER FULLTEXT INDEX ON YourTableName START FULL POPULATION

And this will do an Incremental Population (it does require a column of data type timestamp):
ALTER FULLTEXT INDEX ON YourTableName START INCREMENTAL POPULATION

0
wickedwAuthor Commented:
I have followed Bodestones suggestion and started looking at the job creation code in the profiler, it looks complicated, but will point me in the right direction for now.  I will have to use the GUI in the meantime.  Thanks for your help.

0
wickedwAuthor Commented:
Was hoping someone had a concrete example, but this will help to undercover more.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.