Are you trying to find a solution to SQL Error 11730? We can help you fix it.

This error occurs while trying to use a Sequence object from a database.

Here at Bobcares, we have seen several such SQL related issues as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a look at the cause for this error and see how to fix it.

What causes SQL Error 11730 to occur

Before we get into the solution part, let’s first discuss more on this error.

Sequence objects are the feature using which we can generate numeric values sequentially. However, it has a limitation that you can’t make any sequence as a default value of a column if it resides in any other database.

For instance, here is a sequence object that resides in the database SequenceTest. Here, it should be made as a default value of a column in a table. This table resides in tempdb database.

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

As a result of the above scripts, here is the output obtained that results in 11730 SQL error.

–OUTPUT
Msg 11730, Level 15, State 1, Line 2
Database name cannot be specified for the sequence object in default constraints.

How we fix SQL Error 11730

Now let’s see how our Support Engineers provide a solution to this error.

It is clear that we can’t make any sequence as a default value of a column if it resides in any other database.

However, there is a workaround, where you can still access the sequence even though it is outside the database.

For that, we use a three-part name (SequenceTest.[dbo].[Seq_Sample]). We insert the sequence value in any database using this three-part name.

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

Finally, the above script fixes the SQL error.

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

So far, we discussed the SQL 11730 error and saw an example script. Also, we saw how our Support Engineers find an alternative to this SQL error.