Generated script using .scmp file as source contains unwanted SET ANSI_NULLS OFF commands

[tommi1hirvonen]

• SqlPackage or DacFx Version: 16.0.6296.0
• .NET Framework (Windows-only) or .NET Core: .NET Core
• Environment (local platform and source/target platforms): Windows 11

Steps to Reproduce:

1. Create a schema compare file (.scmp) comparing a database project to an empty database.
2. Generate a publish script using the .scmp file as a source instead of the .dacpac file.
3. The generated script includes unwanted SET ANSI_NULLS OFF commands before each object's create commands.
4. Build the project to generate a .dacpac file.
5. Generate a publish script using the .dacpac file a source and the same target database.
6. The generated script does not include the SET ANSI_NULLS OFF commands before each object.

Table definition in project:

CREATE TABLE [stg].[XML_ExchangeRates] (
    [Currency] NVARCHAR (50) NULL,
    [Rate]     FLOAT (53)    NULL,
    [Date]     DATE          NULL
);

Script generated using .scmp:

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS OFF;
GO
CREATE TABLE [stg].[XML_ExchangeRates] (
    [Currency] NVARCHAR (50) NULL,
    [Rate]     FLOAT (53)    NULL,
    [Date]     DATE          NULL
);

Script generated using .dacpac

CREATE TABLE [stg].[XML_ExchangeRates] (
    [Currency] NVARCHAR (50) NULL,
    [Rate]     FLOAT (53)    NULL,
    [Date]     DATE          NULL
);

I am using an SDK-style database project, but I think this also applies to non-SDK-style projects.

The SET ANSI_NULLS OFF commands are unwanted, since they are most often not the expected configuration objects are created in. Tables created in ANSI_NULLS OFF mode have some limitations, e.g. you cannot add filtered indexes. More information about this can be found in the documentation. I found this out the hard way after creating some tables with the setting turned off. Ultimately I had to drop and recreate the tables.

I noticed this issue when using the schema compare tool in Azure Data Studio. I created an issue in the ADS repository, but closed it after doing some testing with the SqlPackage.exe CLI. Maybe this is a more correct place for this issue.

Is there a setting to exclude the SET ANSI_NULLS OFF commands from the .scmp generated script? Is there a specific reason why the ANSI_NULl setting is explicitly set to OFF?

[namangupta211]

Hi, After investigating the issue, we found out, that if we do the SchemaCompare using Sqlproj file then only this issue occurs, if we use dacpac to do the SchemaCompare and then generate a publish script using the .scmp file as a source, we won't get the issue. Please try this as a workaround, meanwhile we are working on fixing the issue.

Thanks,

[jantjep917]

Any news regarding this issue? @namangupta211 @llali

[0liver]

This has bitten us, too. It would be great to get this fixed!

[pkoelbl]

This is a big problem for us as well.

[PKleinCbtw]

As a hint: There is a setting in the dedicated Table in the VS Project Browser allowing you to set the ANSI_NULLS value Manually. By setting this to ON i was able to get around this error.

[rtmann]

Not only does this STILL happen, years later... But Azure Data Studio is being replaced by Sql Server Database Projects in VSCode and this bug was carried over and currently exists in the preview of those tools in VSCode, so not even in the new VSCode are we going to get away from this bug.

This is a very big serious bug, if stored procedures are created with ANSI NULLS off, they don't work, all code querying them will crash because all modern connections default to it being on.

This bug really janks up developer flows for rapid development where developers want to push fast schema changes out to the dev azure database quickly without doing a full commit/cidc dacpac deployment.

Schema Compare generates the script like this

GO
SET QUOTED_IDENTIFIER ON;

SET ANSI_NULLS OFF;


GO
-- =============================================
-- sp_GetFileDropHistory
-- Retrieves the status history for a specific file drop
-- =============================================
CREATE PROCEDURE dbo.sp_GetFileDropHistory
....... etc

Regardless of whether you have ansi nulls set for the whole project file or not.

However the workaround works, if you manually set a stored procedure's project inclusion like this

    <Build Include="sprocs\sp_UpdateFileDropStatus.sql">
      <AnsiNulls>On</AnsiNulls>
      <QuotedIdentifier>On</QuotedIdentifier>
    </Build>

Then it will generate a script like this

O
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO

But the default behavior is that it is all off, ignoring the project global settings for it, and developers have to remember to set these to on in the project include xml. You don't get a fancy properties window in VSCode like you do in Visual Studio so this requires manual XML edits in the project file and is a SUB PAR developer experience. And needing visual studio is also subpar, because the whole point to the new SSDT tools in VSCode is to have tooling that works normally in dotnet build cidc/pipelines and runs on mac/windows/linux with cross platform .net stacks with modern .net.

Making developers use visual studio to do database management is basically a guaranteed way to have them go find a better product.

This really should be fixed, PLEASE we all want the new VSCode Database Projects to be AMAZING and the best way to manage MSSQL database schemas in 2025, but you have to put some more TLC into this stack to get it there, and invest some more resources into it.

[pthivierge-sayona]

This is a real problem and must be fixed, can you please provide a timeline?

[pthivierge]

@dzsquared , any chance this coulb be looked at? otherwise what workaround would you recomment? Thanks a lot!