Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_fulltext_service

  No additional text.


Syntax
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_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 ('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)


    -- 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

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash