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:
- Make a temporary table and copy the existing table's contents into the temporary table
- Truncate the existing table
- Let SSDT update the data type
- Fill the data back in from the temporary table
That seems horribly clunky and inefficient, though.
Is there a better alternative?
Best Answer
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.
Depending on the nature of the target you may of course need to take care of dropping and recreating foreign key constraints.