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;    

Stinger

possible duplicate of Allow (once) column drop in database project – Kaiser

Tried unchecking the Block incremental deployment if data loss might occur and I still get the same error – Stinger

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.

Timecard

If the requirement is just to drop a column, why do we need to specify #2? IMO, #2 is risky. – Munguia

worked in VS2019 – Schofield

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

Turaco

So it's actually possible to control this option from both the properties of the project, and the publish settings? – Churchill

but I stuck in to another problem or error when reducing the size of column from nvarchar-5 to nvarchar-3 with having data into the column The type for column vs_column in table [dbo].[tblUsers] is currently NVARCHAR (5) NULL but is being changed to NVARCHAR (3) NULL. Data loss could occur. – Denationalize

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.

Gazetteer

but I stuck in to another problem or error when reducing the size of column from nvarchar-5 to nvarchar-3 with having data into the column The type for column vs_column in table [dbo].[tblUsers] is currently NVARCHAR (5) NULL but is being changed to NVARCHAR (3) NULL. Data loss could occur. – Denationalize

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

Cohin

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.

Sadye

What happens when you generate a script to do this action? Can you post that or the appropriate sections of the script that try to drop the column? I've done things like this with SSDT before and haven't had this issue unless the option to not allow data loss was checked. – Sadye

Ok part of the problem is the default constraint placed on the column... I deleted it first then dropped the column and it worked. However next question is, what if it's not a default constraint, but some other type. Is there a way to drop ALL constraints on a given column? – Stinger

I don't think there's an easy way to do it like when you rename a column. I do know that if you drop the column your build of the project should completely fail if there are dependencies on it in the same database. They should also show up in error/warning messages. You can double-click those to take you to the respective files and either comment out the constraint or delete the file/text for it. – Sadye

Wierd, so I used a pre-deployment script to drop the columns, because I have to drop the default constraint first, then drop the column. I have the Block Incremental Deployment if data loss might occur" UNCHECKED. I STILL get the error stated in the original question. Is there any other setting in the Advanced section that would cause this??? BUT THE COLUMNS GET DROPPED! – Stinger

Not sure why you're dropping in the pre-deploy script. The project should remove the column when you publish it, but not being able to find the column because it's already been dropped could give you fits. The script to do the work is generated prior to actually running the pre/post-deploy scripts; therefore, the publish operation will try to drop the column within the script. Try taking the "drop column" operation out of your pre-deploy script and choose to "Generate Script" when you publish instead of pushing changes directly. – Sadye

And if you drop the default constraints and columns from the project, both should be removed as part of the normal publish operations. – Sadye

Took everything out of the predeploy script... Published and chose to Generate Script against my target DB... In the generate script the following line is generated IF EXISTS (select top 1 1 from [dbo].[Discounts]) RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT – Stinger

There is data in the table Discounts where we are trying to drop the table column, but WHAT on earth is generating that line if I have the block incremental updates if data loss occurs Unchecked – Stinger

I Did something in another table, and it generated the same line in the publish script for that table... so it's doing this for any table I drop a column for, it's checking for data in the table before the alter statements in the published script... there has to be some other setting that is telling it to do that. – Stinger

OK, So I did a schema Comparison and in Options Unchecked the Block on data loss... Generating the script from the schema compare DID NOT generate the check for rows lines in the resulting sql. However at the PROJECT SETTINGS level under debug, there is the same setting, but if you right click the project and publish, then generate script from there it will ALWAYS generate the check for rows sql in the resulting generated script. It's like it's ignoring the project level setting under DEBUG->Deployment options – Stinger

Try creating a publish profile, especially if you'll be doing this a lot. (When you choose to publish, set up your options and save the file as something. You can then just use those options in the future very quickly.) schottsql.blogspot.com/2012/11/… should give you some ideas, if a little dated by now. – Sadye

Actually had to add the flag to the command line params call that executes the dacpac, that did it... We don't do our deployments from within VS. sheesh! – Stinger

Well, for test purposes it can work. Also, you can use publish profiles when you release. We do that all the time and have one set up for each of our environments. That article above lists the commands we use for the publish profile. If you substitute the "publish" option for some Generate Script options, it can work reasonably well to see what will happen. – Sadye