Synapse DB deployment using dacpac returning error on with default constraint

Question

Hi

I have a table where the structure of table is modified and table uses dynamic data masking. The table has data in it for which a tmp table is getting created during deployment.

During deployment using Azure Devops, I am getting the following error

Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 104338, Level 16, State 1, Line 1 An expression cannot be used with a default constraint. Specify only constants for a default constraint.

Here is the create table script

CREATE TABLE employee.tmp_ms_xx_leave_request

(

employee_id VARCHAR (100) MASKED WITH (FUNCTION = 'default()') NOT NULL,

start_dt datetime MASKED WITH (FUNCTION = 'default()') NOT NULL,

end_dt datetime MASKED WITH (FUNCTION = 'default()') NOT NULL,

leave_type VARCHAR (25) MASKED WITH (FUNCTION = 'default()') NOT NULL,

approved_by VARCHAR (100) MASKED WITH (FUNCTION = 'default()') NULL,

workflow_id int MASKED WITH (FUNCTION = 'default()') NULL,

leave_hour int MASKED WITH (FUNCTION = 'default()') NULL,

leave_day int MASKED WITH (FUNCTION = 'default()') NULL,

create_dt datetime MASKED WITH (FUNCTION = 'default()') NOT NULL,

modified_dt datetime MASKED WITH (FUNCTION = 'default()') NOT NULL

)

and error logs table script is as below.

##[error]CREATE TABLE [employee].[tmp_ms_xx_leave_request] (

##[error] employee_id VARCHAR (100) MASKED WITH (FUNCTION = 'default()') NOT NULL,

##[error] start_dt datetime MASKED WITH (FUNCTION = 'default()') NOT NULL,

##[error] end_dt datetime MASKED WITH (FUNCTION = 'default()') NOT NULL,

##[error] leave_type VARCHAR (25) MASKED WITH (FUNCTION = 'default()') NOT NULL,

##[error] approved_by VARCHAR

##[error]System.Management.Automation.RemoteException

##[error]The Azure SQL DACPAC task failed. SqlPackage.exe exited with code 1.

Looks to me that it is giving error on data masking function on a null field. Strangely if I drop the table and leave the devops to run it identifies missing table and creates it with new structure without issues whereas it is unable to alter the table.

Has anyone seen such an error?

Answer 1

Hi Gayatri K ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

I understand that you are facing an error while deploying a Synapse DB using dacpac.

The error message indicates that an expression cannot be used with a default constraint.

• To resolve this error , could you please try to modify the create table script to use a constant value for the default constraint instead of an expression. For example, instead of using MASKED WITH (FUNCTION = 'default()') NULL, please try to use MASKED WITH (FUNCTION = 'default(0)') NULL to set the default value to 0.
• Additionally, please check if the version of the SQL Server Data Tools (SSDT) you are using is compatible with the version of the SQL Server they are deploying to. If the versions are not compatible, it can cause deployment issues.
• Also, please ensure that all the necessary permissions and access rights to deploy the Synapse DB using dacpac are in place.
• Deploying Synapse SQL Serverless objects across environments using SSDT explains how to deploy a Synapse DB using dacpac .

I hope this helps! Please let us know how it goes. Thankyou