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

Answer 3

In my situation...

Using Schema Compare in Visual Studio 2015. After you have the Schema Compare window open:

1. Schema Compare Options( the little gear icon in the menu strip).
2. Go to the General Tab
3. Uncheck Block on Possible Data Loss

This remains un-checked for the duration of the session. I did not confirm whether or not it remains checked when you exit the session.

Answer 4

If you can't uncheck 'Block incremental deployment if data loss might occur' for project related reasons, you can knock up a pre deploy and post deploy script to enable the transform to take place. This does get a lot more complex than below if you have dependencies on any of the columns in the upgraded table.

Example pre-deploy script:

IF EXISTS (SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'YourSchema'
    AND TABLE_NAME = 'YourTable'
    AND COLUMN_NAME = 'ColumnToBeDropped')
BEGIN

    CREATE TABLE [Upgrade].[YourTable](
        [YourTableId] [int] NOT NULL,
        [KeepThisColumn] [money] NOT NULL)

    INSERT INTO Upgrade.YourTable
    (YourTableId, KeepThisColumn)
    SELECT YourTableId, KeepThisColumn
    FROM YourSchema.YourTable

    TRUNCATE TABLE YourSchema.YourTable

END

Then the database project will remove the column to be dropped from your database.

You'll need a post deploy script to transfer the data back into the real table. Example post deploy script:

IF EXISTS (SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'Upgrade'
    AND TABLE_NAME = 'YourTable')
BEGIN

    SET IDENTITY_INSERT YourSchema.YourTable ON

    INSERT INTO YourSchema.YourTable
    (YourTableId, KeepThisColumn)
    SELECT YourTableId, KeepThisColumn
    FROM Upgrade.YourTable

    SET IDENTITY_INSERT YourSchema.YourTable OFF

    DROP TABLE Upgrade.YourTable

END

Answer 5

You need to only do it once in the project (not in the pre-deploy script) and when you publish, you'll need to do so with the option to "allow data loss". I don't see a way around possible data loss if you're dropping a column. You can always change that setting back once this is done.