I have a SQL Server Data Tools (VS2012) project that's published automatically during the build process. A column was recently updated from an
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?