Variable History_Retention_Period for SQL Server System Versioned Tables

Question

I want to be able to setup different retention periods for different environments without having a separate script for each environment. I am currently using the SQL Server Database project in Visual Studio and creating a post deployment script to handle this.

I've setup a Sql cmd variable that gets passed based on the publish profile that I am using that pass in a variable called RetentionPolicyMonths:

 <ItemGroup> <SqlCmdVariable Include="RetentionPolicyMonths"> <Value>36</Value> </SqlCmdVariable> </ItemGroup>

I would then like to use that in my post deployment script to set the retention policy per table:

DECLARE @RetentionPolicyMonths int = CAST('$(RetentionPolicyMonths)' AS int) ALTER TABLE [benefits].[Address] SET (SYSTEM_VERSIONING = ON (History_Retention_Period = @RetentionPolicyMonths MONTHS));

My issue here is that it will not currently let me set the History_Retentin_Period property using a variable the way I am doing it now. This is the error that I am currently seeing:

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '@RetentionPolicyMonths'.Expecting -, ID, INT, or NUMERIC

Can I set this using a variable, or can I only supply the number?

Charlieface · Accepted Answer · 2021-07-21 21:17:32Z

The syntax for that ALTER TABLE option is as follows

[, HISTORY_RETENTION_PERIOD = { INFINITE | number {DAY | DAYS | WEEK | WEEKS | MONTH | MONTHS | YEAR | YEARS } } ]

So it looks like you cannot use a variable in that statement.

You can still pass a parameter in, if you use dynamic SQL

DECLARE @sql nvarchar(max) = ' ALTER TABLE [benefits].[Address] SET (SYSTEM_VERSIONING = ON (History_Retention_Period = ' + CAST(@RetentionPolicyMonths AS varchar(10)) + ' MONTHS)); '; EXEC (@sql);

But you appear to actually be interpolating the value into the query anyway (this is not the same as passing a parameter), so you may as well just interpolate that directly, so long as you are sure of its veracity.

ALTER TABLE [benefits].[Address] SET (SYSTEM_VERSIONING = ON (History_Retention_Period = '$(RetentionPolicyMonths)' MONTHS));

Collectives™ on Stack Overflow

Variable History_Retention_Period for SQL Server System Versioned Tables

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related