Forum Discussion

SezMe's avatar
SezMe
Copper Contributor
Oct 25, 2024

Using a Sequence

Consider this table: CREATE TABLE [Events]( [EventID] [int] NULL, << Other columns >> and this Sequence: CREATE SEQUENCE [NewEventID] AS [int] START WITH 1 INCREMENT BY 1 MINVALUE ...
Insert Row
sequence
t-sql
SezMe's avatar
SezMe
Copper Contributor
Oct 25, 2024

rodgerkongI ran Example E in the page for "NEXT VALUE FOR" and got the same error message so the problem has nothing to do with my query.  Now I am really at a loss....

rodgerkong's avatar
rodgerkong
Iron Contributor
Oct 26, 2024
That's weird, did you check the permissions? Could you create a new db, grant db_owner role to do the test? And could you post whole code that you tested fail.
  • SezMe's avatar
    SezMe
    Copper Contributor
    Oct 27, 2024

    rodgerkongI created a new database called AAA.  I then created a new query and inserted these two lines:

    USE AAA
GO

    Then I did a copy/paste of the code in Example A.  Result: Same error message.

    What permissions do I need to check?  On the whole database?  Something else?  How?

     

    Regarding the last question: Consider me a competent novice at SQL Server.  I know how to work in SSMS but details such as permissions have not yet been gained.

    • rodgerkong's avatar
      rodgerkong
      Iron Contributor
      Oct 27, 2024

      SezMe 

      The permissions is OK, you create the db, you are the db owner.
      Could you run these 3 script and post their results?

      SELECT @@VERSION

      and

      EXEC sp_helpdb  'AAA'

      and

      USE AAA
GO
SELECT * FROM sys.sequences
      • SezMe's avatar
        SezMe
        Copper Contributor
        Oct 28, 2024

        rodgerkong  Here is the SQL:

         

         

        USE AAA
GO
SELECT @@VERSION
GO
EXEC sp_helpdb  'AAA'
GO
SELECT * FROM sys.sequences
GO


USE PIM
GO
EXEC sp_helpdb 'PIM'
GO
SELECT * FROM sys.sequences
GO

         

        PIM is the name of the app that is causing the problem.

        Here are the results:

        --------------------------------------------

        Microsoft SQL Server 2019 (RTM-GDR) (KB5046056) - 15.0.2125.1 (X64)
        Sep 20 2024 13:52:26
        Copyright (C) 2019 Microsoft Corporation
        Developer Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 26100: ) (Hypervisor)

        --------------------------------------------

        name db_size owner dbid created status compatibility_level
        AAA 80.00 MB ZEKE-XI\Roger 7 Oct 28 2024 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=904, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 150

        --------------------------------------------

        name fileid filename filegroup size maxsize growth usage
        AAA 1 C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AAA PRIMARY 8192 KB Unlimited 65536 KB data only
        AAA_log 2 C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AAA_Log NULL 73728 KB 2147483648 KB 65536 KB log only

        --------------------------------------------

        << Nothing returned >>

        --------------------------------------------

        name db_size owner dbid created status compatibility_level
        PIM 146.38 MB ZEKE-XI\Roger 9 Feb 20 2024 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=904, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 130

        --------------------------------------------

        name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published start_value increment minimum_value maximum_value is_cycling is_cached cache_size system_type_id user_type_id precision scale current_value is_exhausted last_used_value NewEventID 997682702 NULL 1 0 SO SEQUENCE_OBJECT 2024-10-25 07:20:42.907 2024-10-25 07:20:42.907 0 0 0 1 1 1 2147483647 0 0 NULL 56 56 10 0 1 0 NULL

Resources