How to set change tracking on a Visual Studio database project (SSDT)

Question

I have a SQL Server 2005 DB project and am looking to deploy the Schema over an existing DB that is on a later version of SQL Server. The issue I have is that Change Tracking is enabled on the DB I wish to deploy to and so the first thing SSDT wants to do is disable CT. This poses a problem as I get the error below:

(43,1): SQL72014: .Net SqlClient Data Provider: Msg 22115, Level 16, State 1, Line 5 Change tracking is enabled for one or more tables in database 'Test'. Disable change tracking on each table before disabling it for the database. Use the sys.change_tracking_tables catalog view to obtain a list of tables for which change tracking is enabled. (39,0): SQL72045: Script execution error. The executed script:

IF EXISTS (SELECT 1
       FROM   [master].[dbo].[sysdatabases]
       WHERE  [name] = N'$(DatabaseName)')
BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET CHANGE_TRACKING = OFF 
        WITH ROLLBACK IMMEDIATE;
END

In an effort to get around this I have created a PreDeployment script that executes the below:

/* Run pre-deployment scripts to resolve issues */
IF(SELECT SUBSTRING(@@VERSION, 29,4)) = '11.0'

BEGIN

PRINT 'Enabling Change Tracking';

DECLARE @dbname VARCHAR(250)
SELECT @dbname = DB_NAME()

EXEC('
        IF NOT EXISTS(SELECT * FROM [master].[dbo].[sysdatabases] WHERE name = ''' + @dbname + ''')
        
        ALTER DATABASE ['+ @dbname +
        ']SET CHANGE_TRACKING = ON
        (CHANGE_RETENTION = 5 DAYS, AUTO_CLEANUP = ON);
');

EXEC('
IF NOT EXISTS(SELECT * FROM sys.change_tracking_tables ctt
          INNER JOIN sys.tables t ON t.object_id = ctt.object_id
          INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
          WHERE t.name = ''TableName'')
BEGIN
    ALTER TABLE [dbo].[TableName] ENABLE CHANGE_TRACKING;
END;');

So based on the DB Version Change Tracking is set to enabled on the DB and relevant Tables assuming it is not already enabled.I got this idea from a previous post: # ifdef type conditional compilation in T-SQL sql server 2008 2005

Unfortunately this is still not working as SSDT is trying to disable Change Tracking before the PreDeployment script is executed.

Answer 1

Make sure change tracking is enabled in your database project.

Open your database project's properties > Project Settings > Database Settings... > Operational tab > check the "Change tracking" option