Forum Discussion

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

Iron Contributor

Oct 25, 2024

SezMe

There are more limitations than the message mentioned, This article descripted https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16#limitations-and-restrictions

Since we can't see the context that you called sp, guess there might be having SET ROWCOUNT ON or something else.

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