重新播种时在SP上出错 [英] Getting an error on a SP while Reseeding

问题描述

我正在尝试获取每个表的MaxID并在每个数据库中重新设置它。以下是我的商店程序。



我收到此错误：

Hi,

I am trying to get the MaxID of each table and reseed it in each database. Below is my store procedure.

I get this error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

请帮助：

Please help:

ALTER PROCEDURE [dbo].[ClientDBRESEED]

AS

BEGIN

  DECLARE @DBName varchar(256)
  DECLARE @RESEEDTEXT nvarchar(max)
  DECLARE @VARMAXID nvarchar(max)
  DECLARE @RESEEDCOUNT int
  DECLARE @varSQL1 varchar(max)
  DECLARE @ParmDefinition nvarchar(500);
  DECLARE @retval int
  DECLARE @RESEED varchar(100)
  DECLARE @varSQL varchar(512)
  DECLARE @getDBName CURSOR
  SET @getDBName = CURSOR FOR
  SELECT
    name
  FROM Master.dbo.SysDatabases
  WHERE name LIKE 'T0%'
  DECLARE @GetTbName CURSOR



  OPEN @getDBName
  FETCH NEXT FROM @getDBName INTO @DBName
  WHILE @@FETCH_STATUS = 0
  BEGIN
    PRINT @DBName
    --AcctHolder
    SET @VARMAXID = N'Select @id= Max(ID) from ' + @DBName + '.dbo.AcctHolder'
    SET @ParmDefinition = N'@id int OUTPUT'

    EXEC sp_executesql @VARMAXID,
                       @ParmDefinition,
                       @id = @retval OUTPUT


    PRINT @retval

    SET @RESEEDCOUNT = @retval + 20000

    SET @RESEED = CAST(@RESEEDCOUNT AS varchar(100))
    PRINT @RESEEDCOUNT
    SET @varSQL1 = 'DBCC CHECKIDENT (' + @DBName + '.dbo.AcctHolder, reseed, ' + @RESEED + ' )'
    PRINT @varSQL1
    EXEC (@varSQL1)
    --Consent
    SET @VARMAXID = N'Select @id= Max(ID) from ' + @DBName + '.dbo.ConsentAnnual'
    SET @ParmDefinition = N'@id int OUTPUT'

    EXEC sp_executesql @VARMAXID,
                       @ParmDefinition,
                       @id = @retval OUTPUT


    PRINT @retval

    SET @RESEEDCOUNT = @retval + 20000

    SET @RESEED = CAST(@RESEEDCOUNT AS varchar(100))
    PRINT @RESEEDCOUNT
    SET @varSQL1 = 'DBCC CHECKIDENT (' + @DBName + '.dbo.ConsentAnnual, reseed, ' + @RESEED + ' )'
    PRINT @varSQL1
    EXEC (@varSQL1)

    FETCH NEXT FROM @getDBName INTO @DBName
  END

  CLOSE @getDBName
  DEALLOCATE @getDBName



END

推荐答案

传递给 CHECKIDENT 的表名需要s是一个字符串：

The table name passed to CHECKIDENT needs to be a string:

SET @varSQL1 = 'DBCC CHECKIDENT (''' + @DBName + '.dbo.AcctHolder'', reseed, ' + @RESEED + ' )'

DBCC CHECKIDENT（Transact-SQL） [ ^ ]

这篇关于重新播种时在SP上出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！