0

I have 2 SQL server databases on a single windows server. the user that is used to login has sufficient rights on both databases. The application is only able to have a single database connection but requires content from both databases. To get data from the other database views or synonyms can be used. That all works fine. The application then needs to write a result into a table that is not in the database where it is connected to. This table has an ID column that requires an unique value which comes from a sequence. The application code actually does a get next value call so it must use the sequence. see below error message from the log.

could not get next sequence value[SQL: select next value for SEQ_xxx as seq]

I found a post where somebody stated that sql server 2012 it was not possible to create a synonym for a sequence, I tried it on 2014 and 2017 without any luck. Does anybody know if it possible or will be possible in a new version?

And does anybody has a suggestion to solve this in a different way. Swapping the connection to the other database will not work, as both databases are updated with sequences.

4
  • 4
    No, per the Remarks in the documentation, a SEQUENCE cannot be a target for a SYNONYM. Commented Jan 12, 2022 at 12:32
  • You can use a three-part name instead of a synonym. In DbA you can use DbB.dbo.SEQ_xxx to access a sequence in another database Commented Jan 12, 2022 at 12:37
  • @PanagiotisKanavos yes I know, but the application assumes the sequence by adding SEQ_ in front of the table name. I cannot override this. Commented Jan 12, 2022 at 12:58
  • @Austin, they are on the same instance. I will look into the linked server solution. Commented Jan 12, 2022 at 12:59

1 Answer 1

1

You might try doing this:

Create a stored procedure that can be called to get the sequence(s)

CREATE OR ALTER PROCEDURE [dbo].[GetNextValueForSomeSequence]
(
    @numberOfNextValueNeeded INT = NULL
)
AS
BEGIN TRY
   SET NOCOUNT ON;
   SET XACT_ABORT ON;

   WITH Pass0 AS (SELECT 1 AS C UNION ALL SELECT 1) --2 rows
       ,Pass1 AS (SELECT 1 AS C FROM Pass0 AS A, Pass0 AS B)--4 rows
       ,Pass2 AS (SELECT 1 AS C FROM Pass1 AS A, Pass1 AS B)--16 rows
       ,Pass3 AS (SELECT 1 AS C FROM Pass2 AS A, Pass2 AS B)--256 rows
       ,Pass4 AS (SELECT 1 AS C FROM Pass3 AS A, Pass3 AS B)--65536 rows
       ,Pass5 AS (SELECT 1 AS C FROM Pass4 AS A, Pass4 AS B)--4,294,967,296 rows
       --I removed Pass5, since I'm only populating the Numbers table to 10,000
       ,Tally AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS Number FROM Pass5)
   SELECT NEXT VALUE FOR [dbo].[SomeSequence_Seq] [SeqValue]
         ,T.Number [RowNum]
      INTO [dbo].[#TempSequence]
      FROM Tally T
     WHERE T.Number <= COALESCE(@numberOfNextValueNeeded, 1);

   SELECT [RowNum], [SeqValue]
     FROM [dbo].[#TempSequence]

   DROP TABLE [dbo].[#TempSequence]

END TRY
BEGIN CATCH

   THROW;
END CATCH;
GO

Create a synonym for it

CREATE SYNONYM [SYN].[GetNextValueForSomeSequence] FOR [database].[dbo].[SomeSequence_Seq]

And use it somewhere like this:

CREATE TABLE [dbo].[#TempSequence]
(
   [RowNum] INT
  ,[SeqValue]  INT
);
INSERT INTO [Import].[#TempSequence] ([RowNum], [SeqValue])
EXEC [SYN].[GetNextValueForSomeSequence] 1;

Then use it from the temp table.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.