Publishing database changes with SSDT that include altering data types of columns

Question

I have a SQL Server Data Tools (VS2012) project that's published automatically during the build process. A column was recently updated from an int to decimal(18,4). As a result of this change, the publishing fails with the error

(49,1): SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 6 Rows were detected. The schema update is terminating because data loss might occur. (44,0): SQL72045: Script execution error. The executed script: /* The type for column QuantityReceived in table [dbo].[Reconciliation_Receiving] is currently INT NOT NULL but is being changed to DECIMAL (18, 4) NOT NULL. Data loss could occur. */

IF EXISTS (select top 1 1 from [dbo].[Reconciliation_Receiving]) RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT An error occurred while the batch was being executed.

I understand why I'm receiving that error, and I know it could be resolved by disabling the "Block Incremental Deploy if Data Loss May Occur" flag. However, there's very strong opposition to disabling that feature, so it's not going to be an acceptable solution.

The only other solution I can think of is to do the following:

1. Make a temporary table and copy the existing table's contents into the temporary table
2. Truncate the existing table
3. Let SSDT update the data type
4. Fill the data back in from the temporary table

That seems horribly clunky and inefficient, though.

Is there a better alternative?

Answer 1

I've been tempted to bypass that flag also but have come down on the side of your co-workers and now try to deal with these issues "correctly". The (marginally) less clunky route is to use pre and post deployment scripts to do the work with a rename.

• Rename the existing table in a pre-deployment script.
• With the existing table missing the table in focus will be created as per the new schema definition.
• In a post-deployment script copy from the renamed original table to the new version.

Depending on the nature of the target you may of course need to take care of dropping and recreating foreign key constraints.

Answer 2

In my case I was removing a column from a table.

The solution provided in this answer did not work for me, it resulted in an invalid object name error during the publish.

I found that it was necessary to copy the rows from table, disable constraint checking and delete the rows in a pre deployment script, and then to copy the rows back into the table with identity insert enabled in the post deployment script.

In Script.PreDeployment.sql:

-- copy and delete dbo.Table1
BEGIN TRY
    IF (EXISTS (
        SELECT * FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Table1_copy'))
    BEGIN
        PRINT 'Dropping Table1_copy'
        DROP TABLE dbo.Table1_copy
    END

    PRINT 'Copying dbo.Table1'

    SELECT @LastID = MAX(ID), @StartID = MIN(ID)
    FROM dbo.Table1

    SET @EndID = @StartID + 1000

    SELECT * 
    INTO dbo.Table1_copy 
    FROM dbo.Table1
    WHERE ID BETWEEN @StartID AND @EndId

    SET @StartID = @EndID + 1

    SET IDENTITY_INSERT dbo.Table1_copy ON

    WHILE @StartID < @LastID
    BEGIN
        SET @EndID = @StartID + 1000

        INSERT dbo.Table1_copy (ID, Column1, Column2, Column3)
        SELECT ID, Column1, Column2, Column3
        FROM dbo.Table1
        WHERE ID BETWEEN @StartID AND @EndId

        SET @StartID = @EndID + 1
    END

    SET IDENTITY_INSERT dbo.Table1_copy OFF

    PRINT 'Copied dbo.Table1 to dbo.Table1_copy'

    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

    PRINT 'Deleting dbo.Table1'
    WHILE EXISTS (SELECT 1 FROM dbo.Table1) 
        DELETE TOP(1000) FROM dbo.Table1
    PRINT 'Deleted dbo.Table1'

    PRINT 'SUCCESS: Copy and delete dbo.Table1'
END TRY
BEGIN CATCH
    EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

    PRINT 'ERROR: Copy and delete dbo.Table1'
    PRINT 'ERROR MESSAGE: ' + ERROR_MESSAGE()
END CATCH
GO

In Script.PostDeployment.sql

DECLARE @StartID BIGINT, @LastID BIGINT, @EndID BIGINT

-- populate dbo.Table1
BEGIN TRY
    PRINT 'Populating dbo.Table1'

    SET IDENTITY_INSERT dbo.Table1 ON

    SELECT @LastID = MAX(ID)
    FROM dbo.Table1_copy

    WHILE @StartID < @LastID
    BEGIN
        SET @EndID = @StartID + 1000

        INSERT dbo.Table1 (ID, Column1, Column2, Column3)
        SELECT ID, Column1, Column2, Column3
        FROM dbo.Table1
        WHERE ID BETWEEN @StartID AND @EndId

        SET @StartID = @EndID + 1
    END

    SET IDENTITY_INSERT dbo.Table1 OFF

    EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

    PRINT 'SUCCESS: Populating dbo.Table1'
END TRY
BEGIN CATCH
    SET IDENTITY_INSERT dbo.Table1 OFF

    PRINT 'ERROR: Populating dbo.Table1'
    PRINT 'ERROR MESSAGE: ' + ERROR_MESSAGE()
END CATCH
GO

Answer 3

ALTER TABLE table_1 ALTER COLUMN [a] decimal (18,2)

Will do the job in this case, much simpler solution for a pre deployment script. I have also found that changing the target before the publish will avoid the data loss error being raised.

Sometimes SSDT will identify ALTER TABLE/ALTER COLUMN solutions and use them in the build, but I don't know the rules to make that dependable from the user's perspective. I don't know why it doesn't use it in this case. There is no chance for data loss with ALTER COLUMN.