April 22, 2012

sp_fulltext_service (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_fulltext_service(nvarchar @action
, sql_variant @value)

MetaData:

 create proc sys.sp_fulltext_service  
@action nvarchar(100) = NULL,
@value sql_variant = NULL
as
set nocount on

-- sp_fulltext_service will run under read committed isolation level --
set transaction isolation level READ COMMITTED

-- CHECK PERMISSIONS (must be serveradmin) --
if (is_srvrolemember('serveradmin') = 0)
begin
raiserror(15247,-1,-1)
return 1
end

-- DISALLOW USER TRANSACTION --
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sys.sp_fulltext_service')
return 1
end

-- initialize full-text configuration table --
declare @fulltext_configuration_table TABLE (
Name nvarchar(100) NOT NULL UNIQUE,
IsDeprecated smallint default 0,
IsVisible smallint default 0,
FNC_Name nvarchar(200),
HasValue smallint default 0,
Type nvarchar(30) default NULL,
NeedRangeCheck smallint default NULL,
minvalue bigint default NULL,
maxvalue bigint default NULL,
dflt_longlong bigint default NULL,
dflt_str nvarchar(1024) default NULL)

-- for resource_usage, we set both index and query performance level to the value. So when we read value we just return
-- index performance level. Index and query performance level are suppose to be same. However, user can directly set
-- index and query performance level and they can be different. Resouce usage is just a setting for backward compatibility.
-- name depre, visi,FNC_Name hasVal, type, rangechk, min, max, dfltl, dftlstr
insert into @fulltext_configuration_table values ('resource_usage', 1, 1, 'FTE_IndexingPerformanceLevel', 1, 'int', 1, 0, 0, NULL, NULL)
insert into @fulltext_configuration_table values ('clean_up', 1, 1, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL)
insert into @fulltext_configuration_table values ('connect_timeout', 1, 1, NULL, 1, 'int', 1, 0, 0, NULL, NULL)
insert into @fulltext_configuration_table values ('data_timeout', 1, 1, NULL, 1, 'int', 1, 0, 0, NULL, NULL)
insert into @fulltext_configuration_table values ('update_languages', 0, 1, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL)
insert into @fulltext_configuration_table values ('restart_all_fdhosts', 0, 0, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL)
insert into @fulltext_configuration_table values ('pause_indexing', 0, 1, 'pause_indexing', 1, 'int', 1, 0, 1, NULL, NULL)
-- The following are config values
insert into @fulltext_configuration_table values ('load_os_resources', 0, 1, 'FTE_LoadOSResources', 1, 'int', 1, 0, 1, 0, NULL)
insert into @fulltext_configuration_table values ('verify_signature', 0, 1, 'FTE_VerifySignature', 1, 'int', 1, 0, 1, 1, NULL)
insert into @fulltext_configuration_table values ('aggressive_break', 0, 0, 'FTE_AggressiveBreak', 1, 'int', 1, 0, 1, 1, NULL)
insert into @fulltext_configuration_table values ('ism_size', 0, 0, 'IFTS_ISMSize', 1, 'int', 1, 1, 16, 0, NULL)
insert into @fulltext_configuration_table values ('batch_size', 0, 0, 'IFTS_BatchSize', 1, 'int', 1, 100, 10000, 0, NULL)
insert into @fulltext_configuration_table values ('outstanding_batches', 0, 0, 'IFTS_OutstandingBatches', 1, 'int', 1, 1, 100, 5, NULL)
insert into @fulltext_configuration_table values ('outstanding_isms', 0, 0, 'IFTS_OutstandingISMs', 1, 'int', 1, 1, 100, 5, NULL)
insert into @fulltext_configuration_table values ('auto_crawl_end_fragments_merge', 0, 0, 'IFTS_AutoCrawlEndMergeFragments', 1, 'int', 1, 2, 50, 5, NULL)
insert into @fulltext_configuration_table values ('fdhost_tracing', 0, 0, 'IFTS_FDHostTracing', 1, 'int', 1, 0, 1, 0, NULL)
insert into @fulltext_configuration_table values ('fdhost_tracing_numberoflogs',0, 0, 'FTE_RetailTracingNumberOfLogs',1, 'int', 1, 1, 500, 10, NULL)
insert into @fulltext_configuration_table values ('fdhost_tracing_tag', 0, 0, 'IFTS_FDHostTracingTag', 1, 'nvarchar', NULL, NULL, NULL, NULL, N'')
insert into @fulltext_configuration_table values ('fdhost_dump', 0, 0, 'IFTS_FDHostDump', 1, 'int', 1, 0, 1, 1, NULL)
insert into @fulltext_configuration_table values ('processor_affinity', 0, 0, 'FTE_ProcessorAffinity', 1, 'int', NULL, NULL, NULL, 0, NULL)
insert into @fulltext_configuration_table values ('crawl_ranges', 0, 0, 'IFTS_NumRanges', 1, 'int', 1, 0, 256, 0, NULL)
insert into @fulltext_configuration_table values ('master_merge_dop', 0, 0, 'IFTS_PmmDop', 1, 'int', 1, 0, 256, 0, NULL)
insert into @fulltext_configuration_table values ('ft_timeout', 0, 0, 'IFTS_FTTimeout', 1, 'int', 1, 10000, 6000000,60000, NULL)
insert into @fulltext_configuration_table values ('max_singledoc_retries', 0, 0, 'IFTS_MaxSingleDocRetries', 1, 'int', 1, 0, 10, 1, NULL)
insert into @fulltext_configuration_table values ('upgrade_option', 0, 1, 'IFTS_CatalogUpgradeOptions', 1, 'int', 1, 0, 2, 2, NULL)
insert into @fulltext_configuration_table values ('fdhost_dump_flags', 0, 0, 'FTE_SQLDumperFlags', 1, 'nvarchar', NULL, NULL, NULL, NULL, N'0x820')
IF SERVERPROPERTY('IsNonGolden') IS NOT NULL
begin
insert into @fulltext_configuration_table values ('mm_partition_count', 0, 0, 'FTE_SQLMMPartitionCount', 1, 'int', 1, 0, 1000, 1000, NULL)
insert into @fulltext_configuration_table values ('merge_range_size_in_bytes', 0, 0, 'FTE_SQLMergeRangeSizeInBytes', 1, 'int', 1, 0, 100 * 1024 * 1024, 100 * 1024 * 1024, NULL)
insert into @fulltext_configuration_table values ('costing_factor', 0, 0, 'IFTS_Costingfactor', 1, 'int', 1, 1, 1000000, 1000, NULL)
end

-- if action is null, we print out all configuration options that are available to user --
if @action is null
begin
select Name as 'configuration name',
CASE IsDeprecated
WHEN 1 THEN 'Yes'
ELSE 'No'
end as 'is deprecated',
Type as 'configuration value type',
minvalue as 'minimum', maxvalue as 'maximum'
from @fulltext_configuration_table
where IsVisible = 1
order by Name
return 0
end

-- utility: list all sp_fullext_service options
if @action = 'show_all'
begin
select Name as 'configuration name',
CASE IsDeprecated
WHEN 1 THEN 'Yes'
ELSE 'No'
end as 'is deprecated',
Type as 'configuration value type',
minvalue as 'minimum', maxvalue as 'maximum'
from @fulltext_configuration_table
order by Name
return 0
end

declare @Name nvarchar(100)
declare @IsDeprecated smallint
declare @FNC_Name nvarchar(200)
declare @HasValue smallint
declare @Type nvarchar(30)
declare @NeedRangeCheck smallint
declare @minvalue bigint
declare @maxvalue bigint
declare @dfltlonglong bigint
declare @dfltstr nvarchar(1024)
declare @sval nvarchar(1024)


-- find configuration option from the table --
select @Name = Name,
@IsDeprecated = IsDeprecated,
@FNC_Name = FNC_Name,
@HasValue = HasValue,
@Type = Type,
@NeedRangeCheck = NeedRangeCheck,
@minvalue = minvalue,
@maxvalue = maxvalue,
@dfltlonglong = dflt_longlong,
@dfltstr = dflt_str
from @fulltext_configuration_table where Name = @action

-- Check if configuration option is exposed to user or Yukon Style options for mssearch internal use (no longer supported)
if (@Name is null)
begin

-- Yukon Style options for mssearch internal use (MSSearch only) options are not supported anymore
-- It has been decided that we throw an error for most of the MSSearch only options (some options will still be supported)
-- These options are not documented and has not been advertised to general public or CSS.
-- The other option is to make it a no-op for options we no longer support but it was decided (Jingweil and deepakp)
-- that we throw an error for the reasons explained above.
--
raiserror(15600,-1,-1,'sys.sp_fulltext_service')
return 1
end

-- if value is null, we print the option value --
if (@value is null)
begin
-- if the option doesn't have value, we will just run the command
if (@HasValue = 0)
begin
-- If it is deprecated, we will not do anything --
if (@IsDeprecated = 1)
begin
-- 21 logs the usage of the deprecated action and increments
-- the appropriate perf counter for this deprecated feature
--
DBCC CALLFULLTEXT(21, @Name)
return 0
end

-- DBCC CALLFULLTEXT (18 ) --
if @action = 'update_languages'
begin
DBCC CALLFULLTEXT (18 ) --
end
else if @action = 'restart_all_fdhosts'
begin
DBCC CALLFULLTEXT (22 )
end
else
begin
-- current only update_languages is non-value option
raiserror(15600,-1,-1,'sys.sp_fulltext_service')
return 1
end
end
else
begin
-- If it is deprecated, we will print 0 as value --
if (@IsDeprecated = 1)
begin
select @Name as 'Configuration Name', 0 as 'Configuration Value'
-- 21 logs the usage of the deprecated action and increments
-- the appropriate perf counter for this deprecated feature
--
DBCC CALLFULLTEXT(21, @Name)
return 0
end

-- print the configuration name and value
DBCC CALLFULLTEXT(2, @FNC_Name, @Name)
end
if @@error <> 0
return 1
end
else
begin
if (@HasValue = 0)
begin
-- try to set an option which don't take a value --
-- invalid parameter, raise error here --
raiserror(15600,-1,-1,'sys.sp_fulltext_service')
return 1
end
else
begin
-- If it is deprecated, we will not do anything --
if (@IsDeprecated = 1)
begin
-- 21 logs the usage of the deprecated action and increments
-- the appropriate perf counter for this deprecated feature
--
DBCC CALLFULLTEXT(21, @Name)
return 0
end

select @sval = convert(nvarchar(1024), @value)
if @@error <> 0
return 1

if (@Type = 'int')
begin
-- convert to int value first --
declare @i bigint
if ltrim(rtrim(lower(@sval))) = N'default'
select @i = @dfltlonglong
else
begin
select @i = convert(bigint, @sval)
if @@error <> 0
return 1
if @i = -1
select @i = @dfltlonglong
end

-- check if the value is in the range --
if ((@NeedRangeCheck = 1) and ( @i is null or (not (@i between @minvalue and @maxvalue) and @i <> @dfltlonglong)))
begin
raiserror(15600,-1,-1,'sys.sp_fulltext_service')
return 1
end
select @value = @i
end
else
begin
if ltrim(rtrim(lower(@sval))) = N'default'
select @value = @dfltstr
else
select @value = @sval
end

if @action = 'pause_indexing'
begin
DBCC CALLFULLTEXT ( 19, @value ) --
end
else
begin
DBCC CALLFULLTEXT ( 1, @FNC_Name, @value) -- Set config value
end
if @@error <> 0
return 1
end
end

-- SUCCESS --
return 0 -- sp_fulltext_service

No comments:

Post a Comment

Total Pageviews