"Object '%' is not a sequence object" error

Question

I'm trying to generate new number with these codes.

CREATE SEQUENCE seq_fisnumarasi  
    START WITH 1  
    INCREMENT BY 1 ;  
GO

First of all, I 've created a sequence.

CREATE PROCEDURE next_fis_number 
AS 
BEGIN
    SELECT NEXT VALUE FOR seq_fisnumarasi
END

Then created this procedure.

public int GetFisNumber()
    {
        var rawQuery = Database.SqlQuery<int>("SELECT NEXT VALUE FOR next_fis_number;");
        var task = rawQuery.SingleAsync();
        int nextVal = task.Result;

        return nextVal;
    }

Added this to my Entity Framework Context.

txtFisNumber.Text = context.GetFisNumber().ToString();

Finally, called the "GetFisNumber()" to my form.

But, I got an error. Here: "Inner Exception 1: SqlException: Object 'next_fis_number' is not a sequence object."

You can't `SELECT` the next value from a procedure; you `EXEC` an SP. You don't need that proc either though, just use the SQL `SELECT NEXT VALUE FOR seq_fisnumarasi;` in your C#. — Thom A, Nov 04 '19 at 11:31
@Larnu Did it already. This is the error: **InvalidOperationException: The specified cast from a materialized 'System.Int64' type to the 'System.Int32' type is not valid** — maliyassi, Nov 04 '19 at 11:35
That error is pretty explicit. You've declared your `SEQUENCE` as `bigint`, but the return type of your function is an `int32`, not an `int64`. — Thom A, Nov 04 '19 at 11:37
The other issue is that you are trying to select the next value from a stored procedure. That isn't how that works. Your procedure gets the next value, then you want to return it (as the correct datatype). Or forget the procedure entire as it is really just an extra step that provides no real benefit or reusability. — Sean Lange, Nov 04 '19 at 14:28

Thom A · Accepted Answer · 2019-11-04T12:21:57.753

As per my comments, you don't need an SP, and you're using the wrong data type (as you declared your SEQUENCE as a bigint). Just use SELECT NEXT against the SEQUENCE object, and declare your function as an int64, not an int32:

public int64 GetFisNumber()
    {
        var rawQuery = Database.SqlQuery<int64>("SELECT NEXT VALUE FOR seq_fisnumarasi ;");
        var task = rawQuery.SingleAsync();
        int64 nextVal = task.Result;

        return nextVal;
    }

Note if you have wrongly declared the datatype elsewhere in your c# as an int32, you will need to change those to an int64 as well.

If you want your SEQUENCE to be an int, not a bigint, then define the data type when you CREATE it:

CREATE SEQUENCE seq_fisnumarasi AS int
    START WITH 1  
    INCREMENT BY 1;

From CREATE SEQUENCE:

A sequence can be defined as any integer type. The following types are allowed.

tinyint - Range 0 to 255

smallint - Range -32,768 to 32,767

int - Range -2,147,483,648 to 2,147,483,647

bigint - Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

decimal and numeric with a scale of 0.

Any user-defined data type (alias type) that is based on one of the allowed types.

If no data type is provided, the bigint data type is used as the default.

Emphasis added to the last line. This is why your SEQUENCE is a bigint, as you omitted the data type.

EzLo · Answer 2 · 2019-11-04T11:46:00.100

You don't need an SP to fetch the next value from a sequence. You should fetch it directly from the sequence itself. This is why the message you get is:

Object 'next_fis_number' is not a sequence object."

Because the NEXT VALUE FOR expects a sequence object, not an SP.

So delete you SP and modify your C# code so it uses this:

SELECT NEXT VALUE FOR seq_fisnumarasi;

For the casting error message, your sequence returns a BIGINT by default because there is no explicit data type. BIGINT requires 8 bytes and is translated as an Int64 or long in C#.

You are casting the result of the SQL to int in C#, which defaults to a 4 size byte (Int32). Since there is data loss between a 8 byte int to a 4 byte int, it can't be converted automatically, hence the error.

To fix it, just cast to the specific 8-byte integer in C#:

var rawQuery = Database.SqlQuery<long>("SELECT NEXT VALUE FOR seq_fisnumarasi;");

Or change your SEQUENCE to return a 4-byte integer:

CREATE SEQUENCE seq_fisnumarasi
    AS INT
    START WITH 1  
    INCREMENT BY 1 ;  
GO

Then I have a new error: **The specified cast from a materialized 'System.Int64' type to the 'System.Int32' type is not valid**. I really don't know how to convert it. — maliyassi, Nov 04 '19 at 11:39

"Object '%' is not a sequence object" error

2 Answers2