Visual Studio 2013 database project drop column

Question

Does anyone know the best way to drop an existing column from the database when there are rows of data in the datatable.

What I tried doesn't seem to want to work. I included a pre deployment script in with the database project that does

GO if exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Mercury.dbo.Discounts' and COLUMN_NAME = 'ColumnToRemove') BEGIN     ALTER TABLE Database.dbo.Table1 Drop Column ColumnToRemove END GO 

Then in the script that created the table in the first place I deleted the column in question from the Create Table Script

When execution of the dacpac was done I get the following

Initializing deployment (Start) *** The column [dbo].[Table1].[ColumnToRemove] is being dropped, data loss could occur. Initializing deployment (Complete) Analyzing deployment plan (Start) Analyzing deployment plan (Complete) Updating database (Start) An error occurred while the batch was being executed. Updating database (Failed) *** Could not deploy package. Warning SQL72015: The column [dbo].[Table1].[ColumnToRemove] is being dropped, data loss could occur. Error 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. Error SQL72045: Script execution error.  The executed script: IF EXISTS (SELECT TOP 1 1            FROM   [dbo].[Table1])     RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127)         WITH NOWAIT;     

Answer 1

I know it is an old thread, but I came across this one when I was facing the same problem.. Someone might still benefit..

Here is what worked for me:

When you right click on the DB project in VS you get the 'Publish Database' dialog. You setup the target server connection and choose the correct database.

Then click on the 'Advanced...' button to open up the 'Advanced Publish Settings'.

1) Uncheck - 'Block incremental deployment if data loss might occur' checkbox. 2) Check - 'DROP objects in target but not in project' 

Click on OK button. Then click on Generate Script button in order for the publish script to be generated.

You could save these settings to a profile file if you want to generate the script frequently.

Answer 2

You need to modify database project properties

1. Go to Project Properties
2. Debug tab
3. remove check of this option "Block incremental deployment if data loss might occur" as in the picture below