I am facing an issue while publishing DACPAC. (SQL 2012) I have table in production that is having null,not null values column. I need to make this column not nullable and drop another column. Since that may not be using in the application.
I tried pre-deployment script to update null column with values, alter column and delete column, but getting error- "Rows were detected. The schema update is terminating because data loss might occur."
Since this is a sensitive production, I don't want to uncheck the publish option 'Block incremental deployment if data loss might occur'.
Please let me know if anyone have any idea?
Let us first understand how DACPACs are deployed, These are the order of tasks
1.Build a empty DB using the schema from *.dacpack file, call it "vNextDB"
2.Compare the production DB to vNextDB, and generate a script that will deploy changes, let us call it deploy.sql (you can actually write this to a file and inspect it manually, look for documentation on "SqlPackage.exe")
3.Run Pre-Deployment Script
4.Run deploy.sql from step (2)
5.Run Post-Deployment Script
Looking at the sequence above, you probably already figured the issue. if not, look at when the deploy.sql script is generated, it is done before the pre-deplyment script is executed, so obviously it does see the NULL values and generates script that backs off from deployment.
You can see 'how' it backs off if you make sqlproject.exe write the deploy.sql to disk and open it in a text editor (i can't say how it does the backing off because each version of sqlpackage.exe does it differently. they keep improving it).
Here are couple of options
A) Split your deployment into two, First deploymentwill have no schema change, it just runs "UPDATE MyTable SET theColumn = '' WHERE theColumn IS NULL" in post/pre deployment step, and the second deployment will have the actual changes
B) Take a step back and think comprehensively about how you are going to handle versioning of your DB schema, you should have a versioning strategy, you should think of backward compatibility and such stuff. (i.e You can introduce new column that is NOT NULL with default values and the DB clients can start using that column, while a old client that cant be updated will still be supported because you are not removing the old column right away)
Even if you choose option A to work around the current issue, long term you should establish a versioning startegy. Every self respecting data store has a versioning strategy :-), you should give one to your DB.