DACPAC Publish fails when altering colum constraint to NOT NULL

[JimmyHjelte]

We use Azure SQL Database deployment Task Version : 1.171.4 to deploy to Azure SQL db, when we tried to deploy a column constraint we got the following error in the log.

2020-10-26T14:30:50.1206693Z *** The column MarketId on table [bdl_DW].[MarketLanguage] must be changed from NULL to NOT NULL. If the table contains data, the ALTER script may not work. **To avoid this issue, you must add values to this column for all rows** or mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option. ... 2020-10-26T14:30:52.0466530Z ##[error]lumn LanguageId on table [bdl_DW].[MarketLanguage] must be changed from NULL to NOT NULL. If the table contains data, the ALTER script may not work. To avoid this issue, you must add values to this column for all rows or mark it as allowing NULL values, or 2020-10-26T14:30:52.0468615Z ##[error] enable the generation of smart-defaults as a deployment option. Warning SQL72016: The column MarketId on table [bdl_DW].[MarketLanguage] must be changed from NULL to NOT NULL. If the table contains data, the ALTER script may not work. To avoid this issue 2020-10-26T14:30:52.0470791Z ##[error], you must add values to this column for all rows or mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option. Error SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 8 Rows were detecte 2020-10-26T14:30:52.0473247Z ##[error]d. 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 [bdl_DW].[MarketLanguage]) RAISERROR** (N'Rows were detected. The schema upda 2020-10-26T14:30:52.0474749Z ##[error]te is terminating because data loss might occur.', 16, 127) WITH NOWAIT;

The database we deploy to does NOT contain any null values in that column but deployment still fails , I know that it is possible to enable GenerateSmartDefaults but we don't want to to this and from the log it looks like code is designed to raise an error if the table contains rows even tough that there are no rows that are in conflict with the NOT NULL constraint.

I believe this to be a bug as the informational message suggest that having no null values in the column would solve this.

[coding-totoro]

Is there any update on this issue?

[GustavoAmerico]

I don't know if this problem should be solved in that repository, but theoretically just add Where @ColumnName is null.

IF EXISTS (SELECT TOP 1 1 FROM <schemaName>.<tableName> where <columnName> is null)
RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127)
WITH NOWAIT;

[dwainbrowne]

You can add the following paramater to the DACPAC /p:GenerateSmartDefaults=true if you're using a CI/CD tool like Azure DevOps

[GustavoAmerico]

I'll try

[Mnior]

@dwainbrowne Why is this issue labeled as a bug?

@GustavoAmerico

I don't know if this problem should be solved in that repository, but theoretically just add Where @ColumnName is null.

DacFx is a versatile tool. For large tables, additional table scanning is not desirable.

Сan come up with a large number of deployment scenarios, but demanding them from DacFx is unproductive.

You can implement your scenario in PreDeploy, whichever is more suitable for your case.

[GustavoAmerico]

@mnior, are you suggesting that a pre deploy be created for each deletion? Isn't this an overengenier for a comparison that dacfx already performs?

[Mnior]

already performs

Don't you understand the performance difference between a simple SELECT and with WHERE ? 😮

If table 1 has billions of rows and there are many such tables and columns, then you can wait for days until the deployment is completed.

Maybe that's why M$ doesn't want to interact with clients? 😡

[GustavoAmerico]

already performs

Don't you understand the performance difference between a simple SELECT and with WHERE ? 😮

If table 1 has billions of rows and there are many such tables and columns, then you can wait for days until the deployment is completed.

Maybe that's why M$ doesn't want to interact with clients? 😡

The process already executes a select, my proposal is to add a condition to avoid false positive.

99% of the time the error occurs, in my projects, there are no records in the column, as the data was migrated and the columns were reset before deploy.