Click here to Skip to main content
16,012,110 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
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:

SQL
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
Posted
Updated 1-Jul-15 8:09am
v5
Comments
virusstorm 1-Jul-15 14:28pm    
Does it error only from the stored procedure or does it error when you try to manually run the SQL that is generated?
vidkaat 1-Jul-15 14:34pm    
I get this error when I try to manually execute the SP.
vidkaat 1-Jul-15 14:48pm    
Its not Exec this statement:
SET @varSQL1 = 'DBCC CHECKIDENT (' + @DBName + '.dbo.AcctHolder, reseed, ' + @RESEED + ' )'
After PRINT @varSQL1 when it tries to execute the @varSQL1 it throws that error.

1 solution

The table name passed to CHECKIDENT needs to be a string:
SQL
SET @varSQL1 = 'DBCC CHECKIDENT (''' + @DBName + '.dbo.AcctHolder'', reseed, ' + @RESEED + ' )'

DBCC CHECKIDENT (Transact-SQL)[^]
 
Share this answer
 
Comments
vidkaat 1-Jul-15 15:13pm    
Thanks a lot. But I found that the PRINT statement does not work now. Is there a reason behind it??
Richard Deeming 1-Jul-15 15:15pm    
And the error message is?
vidkaat 1-Jul-15 16:22pm    
No Error message. Its reseeding the tables. But none of the PRINT statement is working.
Richard Deeming 1-Jul-15 16:29pm    
Are you running the procedure from SQL Management Studio, or through code?
vidkaat 1-Jul-15 19:24pm    
running the procedure from SQL Management Studio

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900