In this post, I am providing solution to use a single Sequence object between different databases.
Generally, we are creating a sequence constraint to assign a sequence object to a particular table, but here table and sequence both are on the same database.
Sometimes, we also require to use a Sequence object of an another database.
Unfortunately, you cannot specify another database name when you are creating a default sequence constraint.
Below is a full demonstration on this error and solution.
First, create one Sequence object:
CREATE SEQUENCE dbo.seq_TestSequenceNumber AS
INT START WITH 1
INCREMENT BY 1;
I have created this sequence into AdventureWorks2012 database.
Now use this created Sequence object into TempDB:
CREATE TABLE dbo.tbl_TestSequence
,CONSTRAINT pk_tbl_TestSequence_ID PRIMARY KEY(ID)
ALTER TABLE dbo.tbl_TestSequence
ADD CONSTRAINT seq_tbl_TestSequence_ID DEFAULT
NEXT VALUE FOR [AdventureWorks2012].[dbo].[seq_TestSequenceNumber]
You will get this error message:
The solution is to use Sequence object with INSERT statement:
INSERT INTO dbo.tbl_TestSequence
(NEXT VALUE FOR [AdventureWorks2012].[dbo].[seq_TestSequenceNumber],'Anvesh')
,(NEXT VALUE FOR [AdventureWorks2012].[dbo].[seq_TestSequenceNumber],'Neevan')
,(NEXT VALUE FOR [AdventureWorks2012].[dbo].[seq_TestSequenceNumber],'Roy')
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.