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 26, 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 26, 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

    • rodgerkong's avatar
      rodgerkong
      Iron Contributor
      Oct 28, 2024
      Can't find anything out of ordinary except there is not a SEQUENCE exists in AAA. I don't know what to do next either. Try it on another SQL SERVER?
  • SezMe's avatar
    SezMe
    Copper Contributor
    Oct 28, 2024

    rodgerkong    &^*((* I posted the results but they don't seem to be here.  I'll post them again ... after I rerun the queries.  :sad:

  • SezMe's avatar
    SezMe
    Copper Contributor
    Oct 27, 2024

    rodgerkong 

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

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

    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 27 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

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

    I hope this data is informative.