SQL-DMO Examples: Full-text Indexing These examples illustrate Microsoft Search full-text index configuration and catalog population.Examples A. Creating a Microsoft Search Full-Text Catalog The example illustrates enabling a Microsoft® SQL Server™ database for participation in Microsoft Search-supported full-text indexing and query. Enabling a database is a two-step process. The application flags the database indicating intended participation, then creates at least one full-text catalog.' Enable the database for full-text indexing prior to adding the ' FullTextCatalog object to the containing collection. Note: Create ' and connect of SQLServer object used is not illustrated in this ' example. oSQLServer.Databases("Northwind").EnableFullTextCatalogs' Create a Microsoft Search full-text catalog. Dim oFullTextCatalog As New SQLDMO.FullTextCatalog oFullTextCatalog.Name = "ftcatNorthwind"' Add the FullTextCatalog object to the collection, creating the ' full-text catalog on the server. oSQLServer.Databases("Northwind").FullTextCatalogs.Add oFullTextCatalogB. Indexing a Table for Full-Text Queries This example illustrates creating a full-text index on a column in a SQL Server table.Dim oTable As SQLDMO.Table' Get the Table object referencing the Northwind..Employees table. ' Note: Create and connect of SQLServer object used is not illustrated ' in this example. Set oTable = oSQLServer.Databases("Northwind").Tables("Employees")' Indicate that Employees will be full-text indexed and use the ' Microsoft Search full-text catalog created in an earlier example. oTable.FullTextCatalogName = "ftcatNorthwind" oTable.UniqueIndexForFullText = "PK_Employees" oTable.FullTextIndex = True' Index the Notes column. oTable.Columns("Notes").FullTextIndex = True' Activate the full-text index on the table. oTable.FullTextIndexActive = TrueC. Populating a Full-Text Catalog This example illustrates launching a full population on an existing Microsoft Search full-text catalog.' Perform a full population on the Microsoft Search full-text ' index catalog created in an earlier example. Note: Create and connect ' of SQLServer object used is not illustrated in this example. Set oFullTextCatalog = _ oSQLServer.Databases("Northwind").FullTextCatalogs("ftcatNorthwind") oFullTextCatalog.Start (SQLDMOFullText_Full)Note Microsoft Search full-text catalog population can be a lengthy task. Applications that allow full-text catalog population should display a busy pointer or other appropriate interface device when using SQL-DMO to direct full-text catalog population. D. Scheduling Population of a Full-Text Catalog When using SQL-DMO, the you can implement scheduled population of a Microsoft Search full-text catalog by creating a SQL Server Agent job. The step(s) of the job execute a Transact-SQL command batch directing catalog population.This example illustrates creating a job that schedules an incremental full-text catalog population for weekly execution at 1:00 A.M. of every Sunday.Dim oJob As New SQLDMO.Job Dim oJobSchedule As New SQLDMO.JobScheduleDim oJobStep As SQLDMO.JobStep Dim oFullTextCatalog As SQLDMO.FullTextCatalogDim iStepID As Long Dim strDatabase As String Dim strExecP1, strExecP2 As StringDim StartYear As String Dim StartMonth As String Dim StartDay As StringstrDatabase = "Northwind"' Transact-SQL command batch implementing incremental population ' for a Microsoft Search full-text catalog. strExecP1 = "EXEC sp_fulltext_catalog '" strExecP2 = "', 'start_incremental'"' Create the SQL Server Agent job. Job name format and category ' designation allow job to appear as a schedule property of the ' catalog when the catalog is viewed in SQL Server Enterprise Manager. ' Note: Create and connect of SQLServer object used not illustrated in ' this example. oJob.Name = "Start_Incremental on Northwind.ftcatNorthwind.[" & _ oSQLServer.Databases("Northwind").ID & _ "." & _ oSQLServer.Databases("Northwind").FullTextCatalogs(1).FullText CatalogID & _"]" oJob.Category = "Full-Text" oSQLServer.JobServer.Jobs.Add oJob
' Alter the job, adding a step populating each full-text catalog ' defined. oJob.BeginAlter iStepID = 1 For Each oFullTextCatalog In _ oSQLServer.Databases("Northwind").FullTextCatalogs Set oJobStep = New SQLDMO.JobStep oJobStep.Name = "Northwind_FullText_Incremental_" & iStepID oJobStep.DatabaseName = strDatabase oJobStep.SubSystem = "TSQL" oJobStep.Command = strExecP1 & oFullTextCatalog.Name & strExecP2 oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure oJobStep.OnSuccessAction = SQLDMOJobStepAction_GotoNextStep oJobStep.StepID = iStepID oJob.JobSteps.Add oJobStep iStepID = iStepID + 1 Next oFullTextCatalogoJob.JobSteps(oJob.JobSteps.Count).OnSuccessAction = _ SQLDMOJobStepAction_QuitWithSuccess oJob.StartStepID = 1 oJob.DoAlter' Alter the job, adding a schedule for full-text catalog population. oJobSchedule.Name = "Northwind_FullText_Incremental"' Schedule start date is today's date. Build the string representing ' the date for SQL-DMO. StartYear = DatePart("yyyy", Date) StartMonth = DatePart("m", Date) StartDay = DatePart("d", Date)If Len(StartMonth) < 2 Then StartMonth = "0" & StartMonth If Len(StartDay) < 2 Then StartDay = "0" & StartDayoJobSchedule.Schedule.ActiveStartDate = StartYear & StartMonth & _ StartDay' Schedule execution for once, each Sunday at 1:00 AM. oJobSchedule.Schedule.ActiveStartTimeOfDay = "10000" oJobSchedule.Schedule.FrequencyInterval = SQLDMOWeek_SundayoJobSchedule.Schedule.FrequencyType = SQLDMOFreq_Weekly oJobSchedule.Schedule.FrequencyRecurrenceFactor = 1' Schedule never expires. oJobSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE oJobSchedule.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIMEoJob.BeginAlter oJob.JobSchedules.Add oJobSchedule oJob.DoAlter' Target the local server to enable the job. oJob.ApplyToTargetServer ("(Local)")
最好看一下SQL SERVER的帮助
A. Creating a Microsoft Search Full-Text Catalog The example illustrates enabling a Microsoft® SQL Server™ database for participation in Microsoft Search-supported full-text indexing and query. Enabling a database is a two-step process. The application flags the database indicating intended participation, then creates at least one full-text catalog.' Enable the database for full-text indexing prior to adding the ' FullTextCatalog object to the containing collection. Note: Create ' and connect of SQLServer object used is not illustrated in this ' example. oSQLServer.Databases("Northwind").EnableFullTextCatalogs' Create a Microsoft Search full-text catalog. Dim oFullTextCatalog As New SQLDMO.FullTextCatalog oFullTextCatalog.Name = "ftcatNorthwind"' Add the FullTextCatalog object to the collection, creating the ' full-text catalog on the server. oSQLServer.Databases("Northwind").FullTextCatalogs.Add oFullTextCatalogB. Indexing a Table for Full-Text Queries This example illustrates creating a full-text index on a column in a SQL Server table.Dim oTable As SQLDMO.Table' Get the Table object referencing the Northwind..Employees table. ' Note: Create and connect of SQLServer object used is not illustrated ' in this example. Set oTable = oSQLServer.Databases("Northwind").Tables("Employees")' Indicate that Employees will be full-text indexed and use the ' Microsoft Search full-text catalog created in an earlier example. oTable.FullTextCatalogName = "ftcatNorthwind" oTable.UniqueIndexForFullText = "PK_Employees" oTable.FullTextIndex = True' Index the Notes column. oTable.Columns("Notes").FullTextIndex = True' Activate the full-text index on the table. oTable.FullTextIndexActive = TrueC. Populating a Full-Text Catalog This example illustrates launching a full population on an existing Microsoft Search full-text catalog.' Perform a full population on the Microsoft Search full-text ' index catalog created in an earlier example. Note: Create and connect ' of SQLServer object used is not illustrated in this example. Set oFullTextCatalog = _ oSQLServer.Databases("Northwind").FullTextCatalogs("ftcatNorthwind") oFullTextCatalog.Start (SQLDMOFullText_Full)Note Microsoft Search full-text catalog population can be a lengthy task. Applications that allow full-text catalog population should display a busy pointer or other appropriate interface device when using SQL-DMO to direct full-text catalog population. D. Scheduling Population of a Full-Text Catalog When using SQL-DMO, the you can implement scheduled population of a Microsoft Search full-text catalog by creating a SQL Server Agent job. The step(s) of the job execute a Transact-SQL command batch directing catalog population.This example illustrates creating a job that schedules an incremental full-text catalog population for weekly execution at 1:00 A.M. of every Sunday.Dim oJob As New SQLDMO.Job Dim oJobSchedule As New SQLDMO.JobScheduleDim oJobStep As SQLDMO.JobStep Dim oFullTextCatalog As SQLDMO.FullTextCatalogDim iStepID As Long Dim strDatabase As String Dim strExecP1, strExecP2 As StringDim StartYear As String Dim StartMonth As String Dim StartDay As StringstrDatabase = "Northwind"' Transact-SQL command batch implementing incremental population ' for a Microsoft Search full-text catalog. strExecP1 = "EXEC sp_fulltext_catalog '" strExecP2 = "', 'start_incremental'"' Create the SQL Server Agent job. Job name format and category ' designation allow job to appear as a schedule property of the ' catalog when the catalog is viewed in SQL Server Enterprise Manager. ' Note: Create and connect of SQLServer object used not illustrated in ' this example. oJob.Name = "Start_Incremental on Northwind.ftcatNorthwind.[" & _ oSQLServer.Databases("Northwind").ID & _ "." & _ oSQLServer.Databases("Northwind").FullTextCatalogs(1).FullText CatalogID & _"]" oJob.Category = "Full-Text" oSQLServer.JobServer.Jobs.Add oJob
' Alter the job, adding a step populating each full-text catalog ' defined. oJob.BeginAlter iStepID = 1 For Each oFullTextCatalog In _ oSQLServer.Databases("Northwind").FullTextCatalogs Set oJobStep = New SQLDMO.JobStep oJobStep.Name = "Northwind_FullText_Incremental_" & iStepID oJobStep.DatabaseName = strDatabase oJobStep.SubSystem = "TSQL" oJobStep.Command = strExecP1 & oFullTextCatalog.Name & strExecP2 oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure oJobStep.OnSuccessAction = SQLDMOJobStepAction_GotoNextStep oJobStep.StepID = iStepID oJob.JobSteps.Add oJobStep iStepID = iStepID + 1 Next oFullTextCatalogoJob.JobSteps(oJob.JobSteps.Count).OnSuccessAction = _ SQLDMOJobStepAction_QuitWithSuccess oJob.StartStepID = 1 oJob.DoAlter' Alter the job, adding a schedule for full-text catalog population. oJobSchedule.Name = "Northwind_FullText_Incremental"' Schedule start date is today's date. Build the string representing ' the date for SQL-DMO. StartYear = DatePart("yyyy", Date) StartMonth = DatePart("m", Date) StartDay = DatePart("d", Date)If Len(StartMonth) < 2 Then StartMonth = "0" & StartMonth If Len(StartDay) < 2 Then StartDay = "0" & StartDayoJobSchedule.Schedule.ActiveStartDate = StartYear & StartMonth & _ StartDay' Schedule execution for once, each Sunday at 1:00 AM. oJobSchedule.Schedule.ActiveStartTimeOfDay = "10000" oJobSchedule.Schedule.FrequencyInterval = SQLDMOWeek_SundayoJobSchedule.Schedule.FrequencyType = SQLDMOFreq_Weekly oJobSchedule.Schedule.FrequencyRecurrenceFactor = 1' Schedule never expires. oJobSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE oJobSchedule.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIMEoJob.BeginAlter oJob.JobSchedules.Add oJobSchedule oJob.DoAlter' Target the local server to enable the job. oJob.ApplyToTargetServer ("(Local)")
These examples illustrate Microsoft Search full-text index configuration and catalog population.Examples
A. Creating a Microsoft Search Full-Text Catalog
The example illustrates enabling a Microsoft® SQL Server™ database for participation in Microsoft Search-supported full-text indexing and query. Enabling a database is a two-step process. The application flags the database indicating intended participation, then creates at least one full-text catalog.' Enable the database for full-text indexing prior to adding the
' FullTextCatalog object to the containing collection. Note: Create
' and connect of SQLServer object used is not illustrated in this
' example.
oSQLServer.Databases("Northwind").EnableFullTextCatalogs' Create a Microsoft Search full-text catalog.
Dim oFullTextCatalog As New SQLDMO.FullTextCatalog
oFullTextCatalog.Name = "ftcatNorthwind"' Add the FullTextCatalog object to the collection, creating the
' full-text catalog on the server.
oSQLServer.Databases("Northwind").FullTextCatalogs.Add oFullTextCatalogB. Indexing a Table for Full-Text Queries
This example illustrates creating a full-text index on a column in a SQL Server table.Dim oTable As SQLDMO.Table' Get the Table object referencing the Northwind..Employees table.
' Note: Create and connect of SQLServer object used is not illustrated
' in this example.
Set oTable = oSQLServer.Databases("Northwind").Tables("Employees")' Indicate that Employees will be full-text indexed and use the
' Microsoft Search full-text catalog created in an earlier example.
oTable.FullTextCatalogName = "ftcatNorthwind"
oTable.UniqueIndexForFullText = "PK_Employees"
oTable.FullTextIndex = True' Index the Notes column.
oTable.Columns("Notes").FullTextIndex = True' Activate the full-text index on the table.
oTable.FullTextIndexActive = TrueC. Populating a Full-Text Catalog
This example illustrates launching a full population on an existing Microsoft Search full-text catalog.' Perform a full population on the Microsoft Search full-text
' index catalog created in an earlier example. Note: Create and connect
' of SQLServer object used is not illustrated in this example.
Set oFullTextCatalog = _
oSQLServer.Databases("Northwind").FullTextCatalogs("ftcatNorthwind") oFullTextCatalog.Start (SQLDMOFullText_Full)Note Microsoft Search full-text catalog population can be a lengthy task. Applications that allow full-text catalog population should display a busy pointer or other appropriate interface device when using SQL-DMO to direct full-text catalog population.
D. Scheduling Population of a Full-Text Catalog
When using SQL-DMO, the you can implement scheduled population of a Microsoft Search full-text catalog by creating a SQL Server Agent job. The step(s) of the job execute a Transact-SQL command batch directing catalog population.This example illustrates creating a job that schedules an incremental full-text catalog population for weekly execution at 1:00 A.M. of every Sunday.Dim oJob As New SQLDMO.Job
Dim oJobSchedule As New SQLDMO.JobScheduleDim oJobStep As SQLDMO.JobStep
Dim oFullTextCatalog As SQLDMO.FullTextCatalogDim iStepID As Long
Dim strDatabase As String
Dim strExecP1, strExecP2 As StringDim StartYear As String
Dim StartMonth As String
Dim StartDay As StringstrDatabase = "Northwind"' Transact-SQL command batch implementing incremental population
' for a Microsoft Search full-text catalog.
strExecP1 = "EXEC sp_fulltext_catalog '"
strExecP2 = "', 'start_incremental'"' Create the SQL Server Agent job. Job name format and category
' designation allow job to appear as a schedule property of the
' catalog when the catalog is viewed in SQL Server Enterprise Manager.
' Note: Create and connect of SQLServer object used not illustrated in
' this example.
oJob.Name = "Start_Incremental on Northwind.ftcatNorthwind.[" & _
oSQLServer.Databases("Northwind").ID & _
"." & _
oSQLServer.Databases("Northwind").FullTextCatalogs(1).FullText
CatalogID & _"]"
oJob.Category = "Full-Text"
oSQLServer.JobServer.Jobs.Add oJob
' Alter the job, adding a step populating each full-text catalog
' defined.
oJob.BeginAlter
iStepID = 1
For Each oFullTextCatalog In _
oSQLServer.Databases("Northwind").FullTextCatalogs Set oJobStep = New SQLDMO.JobStep
oJobStep.Name = "Northwind_FullText_Incremental_" & iStepID
oJobStep.DatabaseName = strDatabase
oJobStep.SubSystem = "TSQL"
oJobStep.Command = strExecP1 & oFullTextCatalog.Name & strExecP2
oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure
oJobStep.OnSuccessAction = SQLDMOJobStepAction_GotoNextStep
oJobStep.StepID = iStepID oJob.JobSteps.Add oJobStep
iStepID = iStepID + 1
Next oFullTextCatalogoJob.JobSteps(oJob.JobSteps.Count).OnSuccessAction = _
SQLDMOJobStepAction_QuitWithSuccess
oJob.StartStepID = 1
oJob.DoAlter' Alter the job, adding a schedule for full-text catalog population.
oJobSchedule.Name = "Northwind_FullText_Incremental"' Schedule start date is today's date. Build the string representing
' the date for SQL-DMO.
StartYear = DatePart("yyyy", Date)
StartMonth = DatePart("m", Date)
StartDay = DatePart("d", Date)If Len(StartMonth) < 2 Then StartMonth = "0" & StartMonth
If Len(StartDay) < 2 Then StartDay = "0" & StartDayoJobSchedule.Schedule.ActiveStartDate = StartYear & StartMonth & _
StartDay' Schedule execution for once, each Sunday at 1:00 AM.
oJobSchedule.Schedule.ActiveStartTimeOfDay = "10000"
oJobSchedule.Schedule.FrequencyInterval = SQLDMOWeek_SundayoJobSchedule.Schedule.FrequencyType = SQLDMOFreq_Weekly
oJobSchedule.Schedule.FrequencyRecurrenceFactor = 1' Schedule never expires.
oJobSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
oJobSchedule.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIMEoJob.BeginAlter
oJob.JobSchedules.Add oJobSchedule
oJob.DoAlter' Target the local server to enable the job.
oJob.ApplyToTargetServer ("(Local)")
The example illustrates enabling a Microsoft® SQL Server™ database for participation in Microsoft Search-supported full-text indexing and query. Enabling a database is a two-step process. The application flags the database indicating intended participation, then creates at least one full-text catalog.' Enable the database for full-text indexing prior to adding the
' FullTextCatalog object to the containing collection. Note: Create
' and connect of SQLServer object used is not illustrated in this
' example.
oSQLServer.Databases("Northwind").EnableFullTextCatalogs' Create a Microsoft Search full-text catalog.
Dim oFullTextCatalog As New SQLDMO.FullTextCatalog
oFullTextCatalog.Name = "ftcatNorthwind"' Add the FullTextCatalog object to the collection, creating the
' full-text catalog on the server.
oSQLServer.Databases("Northwind").FullTextCatalogs.Add oFullTextCatalogB. Indexing a Table for Full-Text Queries
This example illustrates creating a full-text index on a column in a SQL Server table.Dim oTable As SQLDMO.Table' Get the Table object referencing the Northwind..Employees table.
' Note: Create and connect of SQLServer object used is not illustrated
' in this example.
Set oTable = oSQLServer.Databases("Northwind").Tables("Employees")' Indicate that Employees will be full-text indexed and use the
' Microsoft Search full-text catalog created in an earlier example.
oTable.FullTextCatalogName = "ftcatNorthwind"
oTable.UniqueIndexForFullText = "PK_Employees"
oTable.FullTextIndex = True' Index the Notes column.
oTable.Columns("Notes").FullTextIndex = True' Activate the full-text index on the table.
oTable.FullTextIndexActive = TrueC. Populating a Full-Text Catalog
This example illustrates launching a full population on an existing Microsoft Search full-text catalog.' Perform a full population on the Microsoft Search full-text
' index catalog created in an earlier example. Note: Create and connect
' of SQLServer object used is not illustrated in this example.
Set oFullTextCatalog = _
oSQLServer.Databases("Northwind").FullTextCatalogs("ftcatNorthwind") oFullTextCatalog.Start (SQLDMOFullText_Full)Note Microsoft Search full-text catalog population can be a lengthy task. Applications that allow full-text catalog population should display a busy pointer or other appropriate interface device when using SQL-DMO to direct full-text catalog population.
D. Scheduling Population of a Full-Text Catalog
When using SQL-DMO, the you can implement scheduled population of a Microsoft Search full-text catalog by creating a SQL Server Agent job. The step(s) of the job execute a Transact-SQL command batch directing catalog population.This example illustrates creating a job that schedules an incremental full-text catalog population for weekly execution at 1:00 A.M. of every Sunday.Dim oJob As New SQLDMO.Job
Dim oJobSchedule As New SQLDMO.JobScheduleDim oJobStep As SQLDMO.JobStep
Dim oFullTextCatalog As SQLDMO.FullTextCatalogDim iStepID As Long
Dim strDatabase As String
Dim strExecP1, strExecP2 As StringDim StartYear As String
Dim StartMonth As String
Dim StartDay As StringstrDatabase = "Northwind"' Transact-SQL command batch implementing incremental population
' for a Microsoft Search full-text catalog.
strExecP1 = "EXEC sp_fulltext_catalog '"
strExecP2 = "', 'start_incremental'"' Create the SQL Server Agent job. Job name format and category
' designation allow job to appear as a schedule property of the
' catalog when the catalog is viewed in SQL Server Enterprise Manager.
' Note: Create and connect of SQLServer object used not illustrated in
' this example.
oJob.Name = "Start_Incremental on Northwind.ftcatNorthwind.[" & _
oSQLServer.Databases("Northwind").ID & _
"." & _
oSQLServer.Databases("Northwind").FullTextCatalogs(1).FullText
CatalogID & _"]"
oJob.Category = "Full-Text"
oSQLServer.JobServer.Jobs.Add oJob
' Alter the job, adding a step populating each full-text catalog
' defined.
oJob.BeginAlter
iStepID = 1
For Each oFullTextCatalog In _
oSQLServer.Databases("Northwind").FullTextCatalogs Set oJobStep = New SQLDMO.JobStep
oJobStep.Name = "Northwind_FullText_Incremental_" & iStepID
oJobStep.DatabaseName = strDatabase
oJobStep.SubSystem = "TSQL"
oJobStep.Command = strExecP1 & oFullTextCatalog.Name & strExecP2
oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure
oJobStep.OnSuccessAction = SQLDMOJobStepAction_GotoNextStep
oJobStep.StepID = iStepID oJob.JobSteps.Add oJobStep
iStepID = iStepID + 1
Next oFullTextCatalogoJob.JobSteps(oJob.JobSteps.Count).OnSuccessAction = _
SQLDMOJobStepAction_QuitWithSuccess
oJob.StartStepID = 1
oJob.DoAlter' Alter the job, adding a schedule for full-text catalog population.
oJobSchedule.Name = "Northwind_FullText_Incremental"' Schedule start date is today's date. Build the string representing
' the date for SQL-DMO.
StartYear = DatePart("yyyy", Date)
StartMonth = DatePart("m", Date)
StartDay = DatePart("d", Date)If Len(StartMonth) < 2 Then StartMonth = "0" & StartMonth
If Len(StartDay) < 2 Then StartDay = "0" & StartDayoJobSchedule.Schedule.ActiveStartDate = StartYear & StartMonth & _
StartDay' Schedule execution for once, each Sunday at 1:00 AM.
oJobSchedule.Schedule.ActiveStartTimeOfDay = "10000"
oJobSchedule.Schedule.FrequencyInterval = SQLDMOWeek_SundayoJobSchedule.Schedule.FrequencyType = SQLDMOFreq_Weekly
oJobSchedule.Schedule.FrequencyRecurrenceFactor = 1' Schedule never expires.
oJobSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
oJobSchedule.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIMEoJob.BeginAlter
oJob.JobSchedules.Add oJobSchedule
oJob.DoAlter' Target the local server to enable the job.
oJob.ApplyToTargetServer ("(Local)")