Some customer asked us that he is facing SQL Msg 11730 error.

This Article will show you to to fix it.

This error is triggered when a sequence object is trying to access a database.

As part of our Server Support Services, we have helped customer solve SQL related issues on a regular basics.

This context will show you why such error occur and how to get rid of it.

More about SQL Msg 11730 error

In order to generate numerical values sequentially, we use the feature in SQL Server known as Sequence Objects. However, external databases does not apply in this since it cannot access it.

If we take a Sequence object in the same database as the SequenceTest one, here you can see that it is assigned as the default value of the column as shown in the scripts below;

USE [SequenceTest]

GO

CREATE SEQUENCE [dbo].[Seq_sample]

AS [int]

START WITH 1

INCREMENT BY 1

GO

USE tempdb

GO

CREATE TABLE dbo.tbl_sample

(

[ID] int,

[Name] varchar(100)

)

GO

ALTER TABLE dbo.tbl_sample

ADD CONSTRAINT Const_tbl_sample DEFAULT

(NEXT VALUE FOR SequenceTest.[dbo].[Seq_Sample])

FOR [ID];

GO

Once the above script is run, you will get an error message as seen below;

Msg 11730, Level 15, State 1, Line 2

Database name cannot be specified for the sequence object in default constraints.

How to solve SQL Msg 11730 error

As seen in the error message above, it not possible to make any sequence as a default value of a column existing in external databases.
To get around this, we use a three part name as "SequenceTest.[dbo].[Seq_Sample]". See how it works below;

USE tempdb

GO

CREATE TABLE dbo.tbl_sample

(

[ID] int,

[Name] varchar(100)

)

GO

INSERT INTO tbl_sample

VALUES (NEXT VALUE FOR SequenceTest.[dbo].[Seq_Sample],’sqlwork’)

GO

Now the error will be fixed.

Need support in solving SQL errors? We can help you.