DACPAC and SQL Sequence

Question

I have a Visual Studio database project (DACPAC) which includes a number of SQL Sequences. However when I deploy the DACPAC it always resets the sequence value to the default value included in the Create script (in this case 1). e.g.

CREATE SEQUENCE [dbo].[MySequence]
AS INT
START WITH 1
INCREMENT BY 1;

Can anyone suggest a way of either instructing the DACPAC to ignore the sequence start value, or some way of getting the DACPAC to restore the correct value as a post deployment step perhaps?

Thanks in advance

Labiovelar

Answer 1

This is a known problem with sequences when using the SSDT tools. There are a couple of solutions.

1. Ignore sequence objects when publishing.
2. Use a custom deployment filter to ignore the starting value.
3. Use sp_sequence_get_range instead of RESTART WITH to increment the counter after deploying to live.

1. Ignore sequence objects when publishing

This is the simplest option but the most awkward as it means you have to manually deploy sequences. Add the following to your publish profile

<ExcludeSequences>True</ExcludeSequences>

Or, from the command line

/p:ExcludeObjectType=Sequences

---

2. Use a custom deployment filter

First download AgileSqlClub's deployment filter. Then add the following to your deployment profile:

<AdditionalDeploymentContributors>AgileSqlClub.DeploymentFilterContributor</AdditionalDeploymentContributors>
<AdditionalDeploymentContributorArguments>SqlPackageFilter=IgnoreName(Order_No_Seq)</AdditionalDeploymentContributorArguments>

Or, from the command line:

/p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor
/p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreName(Order_No_Seq)"

---

3. Use sp_sequence_get_range

For this instead of using RESTART WITH on the production server to change the start value, use:

DECLARE @range_first_value SQL_VARIANT;
EXEC sp_sequence_get_range
    @sequence_name = 'MySequence',
    @range_size = 1000,
    @range_first_value = @range_first_value OUTPUT;

This way the start value will always match the expected value from the deployment script.

---

Resources

• Connect issue - this link is dead
• Forum post discussing the issue
• MSDN for sp_sequence_get_range
• User Voice/Azure Feedback issue - replaces Connect issue - marked as Planned 2018-03-16

Voltaism

I'm confused. What is the benefit of using sp_sequence_get_range instead of ALTER and RESTART WITH? – Buckingham

It's been a while since I looked at this, but if I remember correctly RESTART WITH would change the START value, which cause DPAC to reset the start value later (thinking it changed). sp_sequence_get range just consumes values from the sequence like normal, thus the start value is left unmodified. Though you would have to test that to confirm. – Voltaism

Answer 2

Somewhat belated but I too have had this issue. Causes a lot of headaches.

I've raised a ticket with Microsoft: https://developercommunity.visualstudio.com/content/problem/732407/dacpac-resets-sequences.html

Our current workaround is to use pre and post deployment scripts to capture all the sequence values before the dacpac is applied and to then reset them back afterwards.

This is far from ideal.

Quesenberry

Answer 3

We struggled with this issue for a long time and eventually discovered that when we correct the sequence after it got reset, this would somehow mark it as dirty, causing it to be reset during the next deployment. It seems to us this is caused by the ALTER statement in the query below:

DECLARE  @sequenceNumber AS NVARCHAR(MAX)
DECLARE  @sql NVARCHAR(MAX)
SELECT TOP 1 @sequenceNumber = CONVERT(VARCHAR, CAST(SUBSTRING([dossier].[DossierNumber], 6, 4) AS INT) + 1)  FROM [Dossier] [dossier] WHERE [dossier].[DossierNumber] LIKE CONCAT(YEAR(GETDATE()), '%') ORDER BY [DossierNumber] DESC
SET @sql = 'ALTER SEQUENCE [BSF_SEQ_DOSSIER_NUMBER] RESTART WITH ' + @sequenceNumber
PRINT @sequenceNumber
PRINT @sql
 
EXEC(@sql)
 
GO

After we run this query (because the sequence got reset during the previous deployment), this will mark the sequence to be reset during the next deployment (which forces us to run this query again, ending in an infinite loop).

With following query, the problem disappears. We only need to run this query once, after which the sequence will no longer be reset during any of the following deployments.

DECLARE  @sequenceNumberTarget AS INT;
DECLARE @sequenceNumberCurrentValue AS INT;
SELECT TOP 1 @sequenceNumberTarget = CONVERT(VARCHAR, CAST(SUBSTRING([dossier].[DossierNumber], 6, 4) AS INT)) FROM [Dossier] [dossier] WHERE [dossier].[DossierNumber] LIKE CONCAT(YEAR(GETDATE()), '%') ORDER BY [DossierNumber] DESC;
SELECT @sequenceNumberCurrentValue = CAST(current_value AS INT) FROM sys.sequences WHERE name = 'BSF_SEQ_DOSSIER_NUMBER';
PRINT CONCAT('Sequence number target: ', @sequenceNumberTarget);
PRINT CONCAT('Sequence number current value: ', @sequenceNumberCurrentValue);
WHILE (@sequenceNumberCurrentValue < @sequenceNumberTarget)
BEGIN
    SET @sequenceNumberCurrentValue = NEXT VALUE FOR [BSF_SEQ_DOSSIER_NUMBER];
END

Swisher

Answer 4

We overcame this issue by creating a proc executed in our post deployment to reset the sequences with the max value

we created our own dbo.sequence table which recorded the table and it primary key column for which a sequence was created, we keep this updated as well if / when we create new tables and sequences

then run this code after deployment

        DECLARE UpdateMaxValue CURSOR FAST_FORWARD
   FOR SELECT DISTINCT 
              TableName
            , PrimaryKeyColumn AS ColumnName
       FROM dbo.Sequence AS A
       WHERE TableName NOT IN ( 'Bulk', 'PartyCd', 'PoolNum' )
       ORDER BY TableName
              , PrimaryKeyColumn;
   OPEN UpdateMaxValue;
   WHILE 1 = 1
    BEGIN
     FETCH NEXT FROM UpdateMaxValue INTO @TableName
                                       , @ColumnName;
     SET @CurrentTime = CONVERT(VARCHAR(25), GETDATE(), 121);
     IF @@FETCH_STATUS <> 0
      BEGIN
       BREAK;
     END;
     SET @SQL = ' 
SET NOCOUNT ON

IF OBJECT_ID(''dbo.' + @TableName + ''') IS NOT NULL
 BEGIN
  RAISERROR(''/* ' + @CurrentTime + ' --  ALTER SEQUENCE [dbo].[' + @TableName + 'Seq] -- */'', 0, 1) WITH NOWAIT;

    DECLARE @MaxValue INT = (SELECT ISNULL(MAX(' + @ColumnName + ') + 2,0) FROM dbo.' + @TableName + ') 
  
    EXEC(''ALTER SEQUENCE [dbo].[' + @TableName + 'Seq] RESTART WITH ''+@MaxValue+'''')   
 END
';
     EXEC (@SQL);
    END;
   CLOSE UpdateMaxValue;
   DEALLOCATE UpdateMaxValue;

Bladderwort