How can I update a column with a null value so that it is not null in SQL Server using DACPAC

Question

I am trying to update a database that is supported and deployed using a database project ( .sqlproj ) in Visual Studio 2012. This is easier with SQL Server Management Studio, but in this case I have to deploy using DACPAC.

What is the correct way to change a column that cannot be reset using DACPAC and without risk of data loss?

A column with a null value has been added to the table. Now I need to post an update that sets the non-null column and sets the default value. Since there are rows in the table, updating is not performed. There is an option to "allow data loss", but this is not an option for us, and this update should not lead to data loss. Here is a simple example that shows the problem:

 CREATE TABLE [dbo].[Hello] ( [Id] INT IDENTITY(100,1) NOT NULL PRIMARY KEY, [HelloString] NVARCHAR(50) NULL , [Language] NCHAR(2) NOT NULL ) 

Now publish this database and add rows, at least one row must be null for HelloString.

Change the table definition:

 CREATE TABLE [dbo].[Hello] ( [Id] INT IDENTITY(100,1) NOT NULL PRIMARY KEY, [HelloString] NVARCHAR(50) NOT NULL DEFAULT 'Hello' , [Language] NCHAR(2) NOT NULL ) 

It is impossible to publish.

Error:

Rows were detected. The schema update completes because data loss may occur.

Then I tried to add a pre-deploy script to set all NULL as "Hello":

 UPDATE Hello SET HelloString = 'Hello' WHERE HelloString IS NULL 

This attempt to publish also fails with the same error. Looking at the automatically created publish script, it’s clear why, but this seems like the wrong behavior.

• Applies NOT NULL BEFORE adding by default
• The script checks for any lines, it doesn't matter if it is null or not.

The tip in the comment (To avoid this problem, you must add values ​​to this column for all rows) does not solve this problem.

 /* The column HelloString on table [dbo].[Hello] 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. */ IF EXISTS (select top 1 1 from [dbo].[Hello]) RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT GO PRINT N'Altering [dbo].[Hello]...'; GO ALTER TABLE [dbo].[Hello] ALTER COLUMN [HelloString] NVARCHAR (50) NOT NULL; GO PRINT N'Creating Default Constraint on [dbo].[Hello]....'; GO ALTER TABLE [dbo].[Hello] ADD DEFAULT 'hello' FOR [HelloString]; 

In SQL Server 2012 (v11.0.5343), SQL Server Data Tools 11.1.31009.1

Answer 1

When publishing dacpac using SSMS, you will not have access to the full set of publishing options available when publishing from SqlPackage.exe or Visual Studio. I would suggest publishing either SqlPackage.exe or Visual Studio and turning on the option "Create smart defaults, if applicable." In the case of SqlPackage.exe, you should run a command, for example:

 "C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /a:publish /sf:"C:\MyDacpac.dacpac" /tcs:"Data Source=MYSERVER;Initial Catalog=MYDATABASE;Integrated Security=true" /p:GenerateSmartDefaults=true 

In the case of Visual Studio, you should check the Generate smart defaults option in the Advanced Publishing dialog box.