12

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

Chris Chilvers
  • 6,429
  • 3
  • 32
  • 53
LeeM
  • 133
  • 1
  • 5

3 Answers3

13

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

SMM
  • 2,225
  • 1
  • 19
  • 30
Chris Chilvers
  • 6,429
  • 3
  • 32
  • 53
  • I'm confused. What is the benefit of using sp_sequence_get_range instead of ALTER and RESTART WITH? – maplemale Aug 27 '20 at 19:19
  • 2
    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. – Chris Chilvers Aug 27 '20 at 21:30
1

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.

Paul
  • 144
  • 1
  • 6
0

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;
Duane Bentley
  • 1