3

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.

Community
  • 1
  • 1
SQuirellingAlong
  • 61
  • 3

2 Answers2

7

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

Keith
  • 20,636
  • 11
  • 84
  • 125
  • Yeah that would be the simple solution but as the project is 2005 this option is not listed. Ideally I would switch the project to 2012 and carry out your suggestion. Unfortunately the project needs to stay 2005 for the foreseeable. – SQuirellingAlong Dec 03 '15 at 16:26
2

As Keith said if you want it in enable it. If you do want to disable it then just run your script before doing the compare so you have a pre-pre-deploy script like:

https://the.agilesql.club/Blog/Ed-Elliott/Pre-Compare-and-Pre-Deploy-Scripts-In-SSDT

If you are disabling it then it is a one off thing so pretty simple.

Other options are to write your own deployment contributor and raising a bug via connect.

Deployment Contributor:

https://the.agilesql.club/blog/Ed-Elliott/2015/09/23/Inside-A-SSDT-Deployment-Contributor

https://github.com/DacFxDeploymentContributors/Contributors

Ed

Ed Elliott
  • 6,666
  • 17
  • 32
  • This looks a useful read and sounds similar to what I have started to try using through Octopus PreDeployment scripts. That said having this earlier in the CI pipeline would make life easier not to mention safer so I will take a look at the post in more detail and try to replicate. – SQuirellingAlong Dec 03 '15 at 16:33