Programmatically alter a sequence in SQL Server

Question

I'm preparing a data migration script in which I need to alter a previously created sequence to a new value.

Here is what I tried so far:

DECLARE @Curr_Sampling_Request_ID INT ;

SELECT @Curr_Sampling_Request_ID = MAX (Sampling_Request_ID)
  FROM [MyDB].[ABC].[T_DATA_Sampling_Requests] ;

DECLARE @Update_Sequence NVARCHAR(512) ;

SET @Update_Sequence = 
       N'ALTER SEQUENCE [S_DATA_Sampling_Requests] RESTART WITH ' + 
       CAST(@Curr_Sampling_Request_ID AS VARCHAR) ;

print('Attempting: ' + @Update_Sequence) ;

EXEC @Update_Sequence ;

Upon execution I got the following error message (printed record also shown):

Attempting: ALTER SEQUENCE [S_DATA_Sampling_Requests] RESTART WITH 2848805 Msg 203, Level 16, State 2, Line 10 The name 'ALTER SEQUENCE [S_DATA_Sampling_Requests] RESTART WITH 2848805' is not a valid identifier.

Note: I also tried without the use of a temporary string without the use of a temporary string, but same error is received.

Whencesoever · Accepted Answer · 2017-03-06 09:42:12Z

1

You used your string query like it were a normal stored procedure. You should do :

EXECUTE sp_executesql @Update_Sequence

answered Mar 6, 2017 at 9:42

Whencesoever

2,30617 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Programmatically alter a sequence in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related