ANSI_NULL_DEFAULT always set to on

Question

Hi it is year 2021 almost 2022

the issue described here is still a problem

ansinulldefault-always-set-to-on-in-database-professional-rtm

Is any official way to control ANSI_NULL_DEFAULT in VS Database Project or SQLpackage.exe or in Azure DevOps YAML task called "SqlDacpacDeploymentOnMachineGroup@0"

Answer 1

In answer to your question, no there is no way to control that option from a database project directly. You can create a post-deploy script to change it back if needed. There are several things like this database projects do automatically which you have no control over. A database project expects you to always use the database project and deployment. So the code stored and verified inside the project is expecting that setting to be on.

That setting only controls columns added to tables without NULL/NOT NULL explicitly set. The simplest answer is to always explicitly set those options in your code. Then the setting does not matter.

Answer 2

That setting is a legacy setting that you should never touch. Like it or, by ANSI, a column is nullable unless a NOT NULL constraint is added. So VS Database Projects is doing the right thing when it does not let you meddle with the setting.

Answer 3

yes, i wish it does not touch it at all

but i see in my delta script (VS 2019)

BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET ANSI_NULL_DEFAULT ON,
        WITH ROLLBACK IMMEDIATE;
END

and all my databases have it unchanged since created (off)

So if it is legacy and ignored - I' fine

but if if change behavior of some procs doing something with NULL values - than that is the problem

I do

              "C:\Program Files\Microsoft SQL Server\150\DAC\bin\**sqlpackage**.exe"    ^
              /action:script ^
              /diagnostics:true ^
              /sourcefile:"$(Pipeline.Workspace)\_drop\Database.dacpac"  ^
              /targetConnectionString:"Data Source=${<!-- -->{ server }}; Initial Catalog=${<!-- -->{ parameters.database }}; User Id=xxx; Password=$(deploypasswordyaml);" ^
              /outputpath:$(Pipeline.Workspace)\changesScript\changesScript_${<!-- -->{ parameters.environment }}.sql ^   
              /p:ScriptDatabaseOptions=False  ^
              /p:IgnoreAnsiNulls=True

To see the script

and also tried

• task: SqlDacpacDeploymentOnMachineGroup@0
  displayName: 'Deploy [${<!-- -->{ parameters.database }}] on [${<!-- -->{ server }}]'
  continueOnError: true
  inputs:
  taskType: 'dacpac'
  #targetMethod: 'server'
  DacpacFile: '"$(Pipeline.Workspace)_drop\Database.dacpac"'
  ServerName: '${<!-- -->{ server }}'
  DatabaseName: '${<!-- -->{ parameters.database }}'
  AdditionalArguments: '/p:GenerateSmartDefaults=True'
  AuthScheme: 'sqlServerAuthentication'
  SqlUsername: ${<!-- -->{ parameters.SQL_UserName }}
  SqlPassword: ${<!-- -->{ parameters.SQL_Password }}

Answer 4

I've also tried to control all database properties (publish profile and project settings) .

did not work