怎样使用TSQL语句全文索引填充计划中遇到的问题，有图

[aierda]

小弟在学习全文索引的过程中，遇到此问题。
这个关于填充的作业计划没有看明白，期待各们大侠指点，谢谢！

[二月十六]

是个存储过程，楼主可以看一下

ALTER PROC dbo.sp_add_job
  @job_name                     sysname,
  @enabled                      TINYINT          = 1,        -- 0 = Disabled, 1 = Enabled
  @description                  NVARCHAR(512)    = NULL,
  @start_step_id                INT              = 1,
  @category_name                sysname          = NULL,
  @category_id                  INT              = NULL,     -- A language-independent way to specify which category to use
  @owner_login_name             sysname          = NULL,     -- The procedure assigns a default
  @notify_level_eventlog        INT              = 2,        -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always
  @notify_level_email           INT              = 0,        -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always
  @notify_level_netsend         INT              = 0,        -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always
  @notify_level_page            INT              = 0,        -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always
  @notify_email_operator_name   sysname          = NULL,
  @notify_netsend_operator_name sysname          = NULL,
  @notify_page_operator_name    sysname          = NULL,
  @delete_level                 INT              = 0,        -- 0 = Never, 1 = On Success, 2 = On Failure, 3 = Always
  @job_id                       UNIQUEIDENTIFIER = NULL OUTPUT,
  @originating_server           sysname           = NULL      -- For SQLAgent use only
AS
BEGIN
  DECLARE @retval                     INT
  DECLARE @notify_email_operator_id   INT
  DECLARE @notify_netsend_operator_id INT
  DECLARE @notify_page_operator_id    INT
  DECLARE @owner_sid                  VARBINARY(85)
  DECLARE @originating_server_id      INT

  SET NOCOUNT ON

  -- Remove any leading/trailing spaces from parameters (except @owner_login_name)
  SELECT @originating_server           = UPPER(LTRIM(RTRIM(@originating_server)))
  SELECT @job_name                     = LTRIM(RTRIM(@job_name))
  SELECT @description                  = LTRIM(RTRIM(@description))
  SELECT @category_name                = LTRIM(RTRIM(@category_name))
  SELECT @notify_email_operator_name   = LTRIM(RTRIM(@notify_email_operator_name))
  SELECT @notify_netsend_operator_name = LTRIM(RTRIM(@notify_netsend_operator_name))
  SELECT @notify_page_operator_name    = LTRIM(RTRIM(@notify_page_operator_name))
  SELECT @originating_server_id        = NULL

  -- Turn [nullable] empty string parameters into NULLs
  IF (@originating_server           = N'') SELECT @originating_server           = NULL
  IF (@description                  = N'') SELECT @description                  = NULL
  IF (@category_name                = N'') SELECT @category_name                = NULL
  IF (@notify_email_operator_name   = N'') SELECT @notify_email_operator_name   = NULL
  IF (@notify_netsend_operator_name = N'') SELECT @notify_netsend_operator_name = NULL
  IF (@notify_page_operator_name    = N'') SELECT @notify_page_operator_name    = NULL

  IF (@originating_server IS NULL) OR (@originating_server = '(LOCAL)')
    SELECT @originating_server= UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))

  --only members of sysadmins role can set the owner
  IF (@owner_login_name IS NOT NULL AND ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 0) AND (@owner_login_name <> SUSER_SNAME())
  BEGIN
    RAISERROR(14515, -1, -1)
    RETURN(1) -- Failure
  END
    
  -- Default the owner (if not supplied or if a non-sa is [illegally] trying to create a job for another user)
  -- allow special account only when caller is sysadmin
  IF (@owner_login_name = N'$(SQLAgentAccount)')  AND 
     (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
  BEGIN
    SELECT @owner_sid = 0xFFFFFFFF   
  END
  ELSE 
  IF (@owner_login_name IS NULL) OR ((ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 0) AND (@owner_login_name <> SUSER_SNAME()))
  BEGIN
    SELECT @owner_sid = SUSER_SID()
  END
  ELSE
  BEGIN
    --force case insensitive comparation for NT users
    SELECT @owner_sid = SUSER_SID(@owner_login_name, 0) -- If @owner_login_name is invalid then SUSER_SID() will return NULL
  END

  -- Default the description (if not supplied)
  IF (@description IS NULL)
    SELECT @description = FORMATMESSAGE(14571)

  -- If a category ID is provided this overrides any supplied category name
  EXECUTE @retval = sp_verify_category_identifiers '@category_name',
                                                   '@category_id',
                                                    @category_name OUTPUT,
                                                    @category_id   OUTPUT
  IF (@retval <> 0)
    RETURN(1) -- Failure

  -- Check parameters
  EXECUTE @retval = sp_verify_job NULL,  --  The job id is null since this is a new job
                                  @job_name,
                                  @enabled,
                                  @start_step_id,
                                  @category_name,
                                  @owner_sid                  OUTPUT,
                                  @notify_level_eventlog,
                                  @notify_level_email         OUTPUT,
                                  @notify_level_netsend       OUTPUT,
                                  @notify_level_page          OUTPUT,
                                  @notify_email_operator_name,
                                  @notify_netsend_operator_name,
                                  @notify_page_operator_name,
                                  @delete_level,
                                  @category_id                OUTPUT,
                                  @notify_email_operator_id   OUTPUT,
                                  @notify_netsend_operator_id OUTPUT,
                                  @notify_page_operator_id    OUTPUT,
                                  @originating_server         OUTPUT
  IF (@retval <> 0)
    RETURN(1) -- Failure
    
    
  SELECT @originating_server_id = originating_server_id 
  FROM msdb.dbo.sysoriginatingservers_view 
  WHERE (originating_server = @originating_server)
  IF (@originating_server_id IS NULL)
  BEGIN
    RAISERROR(14370, -1, -1)
    RETURN(1) -- Failure
  END
    

  IF (@job_id IS NULL)
  BEGIN
    -- Assign the GUID
    SELECT @job_id = NEWID()
  END
  ELSE
  BEGIN
    -- A job ID has been provided, so check that the caller is SQLServerAgent (inserting an MSX job)
    IF (PROGRAM_NAME() NOT LIKE N'SQLAgent%')
    BEGIN
      RAISERROR(14274, -1, -1)
      RETURN(1) -- Failure
    END
  END

  INSERT INTO msdb.dbo.sysjobs
         (job_id,
          originating_server_id,
          name,
          enabled,
          description,
          start_step_id,
          category_id,
          owner_sid,
          notify_level_eventlog,
          notify_level_email,
          notify_level_netsend,
          notify_level_page,
          notify_email_operator_id,
          notify_netsend_operator_id,
          notify_page_operator_id,
          delete_level,
          date_created,
          date_modified,
          version_number)
  VALUES  (@job_id,
          @originating_server_id,
          @job_name,
          @enabled,
          @description,
          @start_step_id,
          @category_id,
          @owner_sid,
          @notify_level_eventlog,
          @notify_level_email,
          @notify_level_netsend,
          @notify_level_page,
          @notify_email_operator_id,
          @notify_netsend_operator_id,
          @notify_page_operator_id,
          @delete_level,
          GETDATE(),
          GETDATE(),
          1) -- Version number 1
  SELECT @retval = @@error

  -- NOTE: We don't notify SQLServerAgent to update it's cache (we'll do this in sp_add_jobserver)

  RETURN(@retval) -- 0 means success
END
GO

[aierda]

楼上的大哥，你给的存储过程缺少sp_verify_category_identifiers和sp_verify_job这两个存储过程， 能否一并提供一下? 谢谢！

[zlw66434180]

感谢分享，学习使用。

[二月十六]

ALTER PROC dbo.sp_verify_category_identifiers
   @name_of_name_parameter [varchar](60),
   @name_of_id_parameter [varchar](60),
   @category_name [sysname] OUTPUT,
   @category_id [INT] OUTPUT
AS
BEGIN
  DECLARE @retval         INT
  DECLARE @category_id_as_char NVARCHAR(36)

  SET NOCOUNT ON

  -- Remove any leading/trailing spaces from parameters
  SELECT @name_of_name_parameter = LTRIM(RTRIM(@name_of_name_parameter))
  SELECT @name_of_id_parameter   = LTRIM(RTRIM(@name_of_id_parameter))
  SELECT @category_name          = LTRIM(RTRIM(@category_name))

  IF (@category_name = N'') SELECT @category_name = NULL

  IF ((@category_name IS NOT NULL) AND (@category_id IS NOT NULL))
  BEGIN
    RAISERROR(14524, -1, -1, @name_of_id_parameter, @name_of_name_parameter)
    RETURN(1) -- Failure
  END

  -- Check category id
  IF (@category_id IS NOT NULL)
  BEGIN
    SELECT @category_name = name
    FROM msdb.dbo.syscategories
    WHERE (category_id = @category_id)
    IF (@category_name IS NULL)
    BEGIN
     SELECT @category_id_as_char = CONVERT(nvarchar(36), @category_id)
      RAISERROR(14262, -1, -1, '@category_id', @category_id_as_char)
      RETURN(1) -- Failure
    END
  END
  ELSE
  -- Check category name
  IF (@category_name IS NOT NULL)
  BEGIN
    -- The name is not ambiguous, so get the corresponding category_id (if the job exists)
    SELECT @category_id = category_id
    FROM msdb.dbo.syscategories
    WHERE (name = @category_name)
    IF (@category_id IS NULL)
    BEGIN
      RAISERROR(14262, -1, -1, '@category_name', @category_name)
      RETURN(1) -- Failure
    END
  END

  RETURN(0) -- Success
END
GO

[二月十六]

另一个太长了，发不上来，自己去这个数据库里找

[aierda]

第一个存储过程中包含了一个所谓的内置函数SERVERPROPERTY，请问这个内置函数的具体的内容在哪里查看呢，我查看了数据库中所有系统函数，始终没有发现SERVERPROPERTY这个函数。

[二月十六]

引用 6 楼 aierda 的回复：

第一个存储过程中包含了一个所谓的内置函数SERVERPROPERTY，请问这个内置函数的具体的内容在哪里查看呢，我查看了数据库中所有系统函数，始终没有发现SERVERPROPERTY这个函数。

看不到代码，可以百度看看具体功能和使用方法

[sxq129601]

EXEC 后面是存储过程

[stelf]

系统存储过程你一个初学者干嘛还得花时间和精力去研究呢？先把用法和用途搞清楚再研究原理嘛

[aierda]

谢谢大家的指点!!