?
Solved

Programmatically Create a Full Text Population Schedule via TSQL

Posted on 2010-03-24
10
Medium Priority
?
510 Views
Last Modified: 2012-05-09
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
0
Comment
Question by:wickedw
  • 5
  • 3
  • 2
10 Comments
 
LVL 10

Expert Comment

by:Bodestone
ID: 28439391
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
 

Author Comment

by:wickedw
ID: 28440385
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
 
LVL 10

Accepted Solution

by:
Bodestone earned 1000 total points
ID: 28442962
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 28468195
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
 

Author Comment

by:wickedw
ID: 28532188
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 28555106
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
 

Author Comment

by:wickedw
ID: 28563603
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 28618439
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
 

Author Comment

by:wickedw
ID: 28664792
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
 

Author Closing Comment

by:wickedw
ID: 31706481
Was hoping someone had a concrete example, but this will help to undercover more.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question