Geek Answers Handbook

DACPAC and SQL Sequence

I have a Visual Studio Database Project (DACPAC) that includes a series of SQL sequences. However, when deploying DACPAC, it always resets the sequence value to the default value included in the Create script (in this case, 1). eg.

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

Can anyone suggest a way to instruct DACPAC to ignore the initial value of the sequence or somehow force DACPAC to restore the correct value as a post-deployment step, maybe?

Thanks in advance

+7
sql-server visual-studio ssdt dacpac sqlpackage
source share
1 answer

This is a known sequence issue when using SSDT tools. There are several solutions.

  • Ignore sequence objects when publishing.
  • Use your own deployment filter to ignore the initial value.
  • Use sp_sequence_get_range instead of RESTART WITH to increase the counter after it is deployed.

1. Ignore sequence objects when publishing

This is the easiest option, but the most inconvenient as it means you need to manually deploy the sequences. Add the following to your published profile:

 <ExcludeSequences>True</ExcludeSequences>

Or from the command line

 /p:ExcludeObjectType=Sequences

2. Use a special deployment filter

First download the AgileSqlClub deployment filter . Then add the following to the 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

To do 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;

That way, the initial value will always match the expected value from the script deployment.

Resources

+6
source share

More articles:


All Articles