sp_sequence_get_range returns error: "Invalid object name '<sequence name>'."

Question

Hi,

I have a call to "sp_sequence_get_range" which returns an error only in databases on one server and not on others.

This is an example taken straight from Microsoft documentation page:
https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-sequence-get-range-transact-sql?view=sql-server-ver15

In my procedure, I am only reading the @FirstSeqNum and @LastSeqNum in my output. No matter what @seqName I pass, I get the same error back. The error seems to be coming from another procedure "sp_sequence_get_range_internal". Any ideas about what is wrong here? I have SQL Server 2016.

Procedure Call:

   DECLARE @seqName	VARCHAR(20),  
       	@FirstSeqNum	SQL_VARIANT,    
       	@LastSeqNum	SQL_VARIANT,   
       	@CycleCount	INT,    
       	@SeqIncr	SQL_VARIANT ,   
       	@SeqMinVal	SQL_VARIANT,    
       	@SeqMaxVal	SQL_VARIANT   
          
       SET @seqName = 'Stream_0'   
       EXEC sys.sp_sequence_get_range    
       	@sequence_name		= @seqName, -- N'Test.RangeSeq',   
       	@range_size		= 5,    
       	@range_first_value	= @FirstSeqNum OUTPUT ,    
       	@range_last_value	= @LastSeqNum OUTPUT ,   
       	@range_cycle_count	= @CycleCount OUTPUT,    
       	@sequence_increment	= @SeqIncr OUTPUT,    
       	@sequence_min_value	= @SeqMinVal OUTPUT,    
       	@sequence_max_value	= @SeqMaxVal OUTPUT  

Error:
Msg 208, Level 16, State 1, Procedure sp_sequence_get_range_internal, Line 1 [Batch Start Line 0]
Invalid object name 'Stream_0'.

Answer 1

It seems you used the example from the link you provided. So you need to CREATE SEQUENCE as the example on that page:

CREATE SCHEMA Test ;
GO

CREATE SEQUENCE Test.RangeSeq
AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 25
CYCLE
CACHE 10
;

And then to execute the sys.sp_sequence_get_range

DECLARE
@FirstSeqNum sql_variant
, @LastSeqNum sql_variant
, @CycleCount int
, @SeqIncr sql_variant
, @SeqMinVal sql_variant
, @SeqMaxVal sql_variant ;

EXEC sys.sp_sequence_get_range
@sequence_name = N'Test.RangeSeq'
, @Rover _size = 5
, @Rover _first_value = @FirstSeqNum OUTPUT
, @Rover _last_value = @LastSeqNum OUTPUT
, @Rover _cycle_count = @CycleCount OUTPUT
, @sequence_increment = @SeqIncr OUTPUT
, @sequence_min_value = @SeqMinVal OUTPUT
, @sequence_max_value = @SeqMaxVal OUTPUT ;

SELECT
@FirstSeqNum AS FirstVal
, @LastSeqNum AS LastVal
, @CycleCount AS CycleCount
, @SeqIncr AS SeqIncrement
, @SeqMinVal AS MinSeq
, @SeqMaxVal AS MaxSeq ;

Please note that you have to provide the name of the sequence object to the parameter @sequence_name, not the string.

Answer 2

We would need to see your CREATE SEQUENCE statement to help you.

Answer 3

Hi @Manasi Ranade ,

Thank you so much for posting here.

Maybe you could even add the database name of schema created too. I created the schema in DB1 and ran the code in DB2, I faced the same error as you.

Please refer below and check whether it is helpful to you.

CREATE SCHEMA Test ;    
GO    
    
CREATE SEQUENCE Test.Stream_0    
    AS int     
    START WITH 1    
    INCREMENT BY 1    
    MINVALUE 1    
    MAXVALUE 25    
    CYCLE    
    CACHE 10  ;   

--Run in another database

DECLARE @seqName    VARCHAR(20),  
     @FirstSeqNum    SQL_VARIANT,    
     @LastSeqNum    SQL_VARIANT,   
     @CycleCount    INT,    
     @SeqIncr    SQL_VARIANT ,   
     @SeqMinVal    SQL_VARIANT,    
     @SeqMaxVal    SQL_VARIANT   
       
 SET @seqName = 'DatabaseName.Test.Stream_0' --modify your database name here  
  
 EXEC sys.sp_sequence_get_range    
     @sequence_name        = @seqName,    
     @range_size        = 5,    
     @range_first_value    = @FirstSeqNum OUTPUT ,    
     @range_last_value    = @LastSeqNum OUTPUT ,   
     @range_cycle_count    = @CycleCount OUTPUT,    
     @sequence_increment    = @SeqIncr OUTPUT,    
     @sequence_min_value    = @SeqMinVal OUTPUT,    
     @sequence_max_value    = @SeqMaxVal OUTPUT  
  
SELECT  @FirstSeqNum AS FirstVal    
, @LastSeqNum AS LastVal    
, @CycleCount AS CycleCount    
, @SeqIncr AS SeqIncrement    
, @SeqMinVal AS MinSeq    
, @SeqMaxVal AS MaxSeq ;    

Output:

FirstVal LastVal CycleCount SeqIncrement MinSeq MaxSeq  
1 5 0 1 1 25  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 4

Maybe you should include the corresponding schema? For example:

SET @seqName = 'MySchema.Stream_0'