SQL SERVER - Same code different result when running SQL script via SQLCMD and via MSSQL

Question

I have this weird issue where same script have different results. Below is my code:

--Create table ExtractForCustomerLoyalty--------------------------------------------------------------------
--This contains the customer extract records and is used by CustomerLoyaltyExporter batch job.
--Version 1 - 8/14/2018
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ExtractForCustomerLoyalty')
BEGIN
    Print 'Creating [dbo].[ExtractForCustomerLoyalty] table...'

    CREATE TABLE [dbo].[ExtractForCustomerLoyalty](
        [rowID] [int] NOT NULL,
        [sequenceCount] [int] NOT NULL,
        [extractTime] [datetime] NOT NULL,
        [extractData] [varchar](8000) NOT NULL,
        [exported] [varchar](1) NOT NULL DEFAULT ('N')
    ) ON [PRIMARY]
END
GO


--Create trigger customer_extract---------------------------------------------------------------------------
--This trigger is used to create customer extract records
--Version 1 - 8/14/2018
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'['+OBJECT_SCHEMA_NAME(object_id)+N'].[customer_extract]') AND [type] = 'TR')
BEGIN
    Print 'Dropping [dbo].[customer_extract] Trigger'
    DROP TRIGGER [dbo].[customer_extract];
END;
GO

CREATE TRIGGER [dbo].[customer_extract] on [CCSV4].[dbo].[ExtractForStandardCustomer]
AFTER INSERT
AS
    SET ANSI_NULLS ON
    SET ANSI_PADDING ON
    SET ANSI_WARNINGS ON
    SET ARITHABORT ON
    SET CONCAT_NULL_YIELDS_NULL ON
    SET QUOTED_IDENTIFIER ON

    --Just in case we need to deactivate trigger
    DECLARE @Cinfo VARBINARY(128)  
    SELECT @Cinfo = Context_Info()  
    IF @Cinfo = 0x55555  
        RETURN

    --Declaration
    DECLARE @rowID int
    DECLARE @dataXML XML
    DECLARE @data VARCHAR(MAX) = ''

    -- Get the complete extractData xml from ExtractForStandardCustomer table
    SET @rowID = (select rowID from inserted)
    SELECT @data = @data + extractData FROM ExtractForStandardCustomer where rowID = @rowID order by sequenceCount
    SET @dataXML = TRY_CAST(@data as XML)

    IF @dataXML IS NULL
        RETURN

    -- Check the CustomerUpdate type (Create / Update / Delete)
    IF (@dataXML.exist('(/CustomerExtract/Change)') = 1)
        -- Customer Update
        INSERT INTO [dbo].[ExtractForCustomerLoyalty](rowID, sequenceCount, extractTime, extractData)
        SELECT i.rowID, i.sequenceCount, i.extractTime,
            '' + '|' +
            '' + '|' +
            ISNULL(@dataXML.value('(//After/Customer/Loyalty/LoyaltyID)[1]', 'VARCHAR(8000)'),'') + '|' +
            ISNULL(@dataXML.value('(//After/Customer/Loyalty/JoinLocation)[1]', 'VARCHAR(8000)'),'') + '|' +
            ISNULL(@dataXML.value('(//After/Customer/Loyalty/JoinDate)[1]', 'VARCHAR(8000)'),'') + '|' +
            ISNULL(@dataXML.value('(//After/Customer/Loyalty/FirstPurchaseDate)[1]', 'VARCHAR(8000)'),'') + '|' +
            ISNULL(@dataXML.value('(//After/Customer/Loyalty/FirstPurchaseLocation)[1]', 'VARCHAR(8000)'),'') + '|'
            ---More here
        FROM inserted i
    ELSE
        BEGIN
        --Customer Create / Delete
        INSERT INTO [dbo].[ExtractForCustomerLoyalty](rowID, sequenceCount, extractTime, extractData)
        SELECT top 1 i.rowID, i.sequenceCount, i.extractTime,
            '' + '|' +  --CUSTOMER_ID (NULL for now)
            '' + '|' +  --HOUSEHOLD_ID (NULL for now)
            ISNULL(@dataXML.value('(//Customer/Loyalty/LoyaltyID)[1]', 'VARCHAR(8000)'),'') + '|' +
            ISNULL(@dataXML.value('(//Customer/Loyalty/JoinLocation)[1]', 'VARCHAR(8000)'),'') + '|' +
            ISNULL(@dataXML.value('(//Customer/Loyalty/JoinDate)[1]', 'VARCHAR(8000)'),'') + '|' +
            ISNULL(@dataXML.value('(//Customer/Loyalty/FirstPurchaseDate)[1]', 'VARCHAR(8000)'),'') + '|' +
            ISNULL(@dataXML.value('(//Customer/Loyalty/FirstPurchaseLocation)[1]', 'VARCHAR(8000)'),'') + '|' +
            ISNULL(@dataXML.value('(//Customer/Name/First)[1]', 'VARCHAR(8000)'),'') + '|'
            --More here

        FROM inserted i
        END
GO

When I run this using SQL Server Management Studio 2014 UI, when I insert something on ExtractForStandardCustomer table, the trigger executes normally. It populates the ExtractForCustomerLoyalty table with the desired records...

However, when I try running the very same script via cmd:

sqlcmd -E -d testDB -i TESTSCRIPT.sql > TESTSCRIPT.log

It throws this error:

 com.microsoft.sqlserver.jdbc.SQLServerException: CONDITIONAL failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Answer 1

Different tools and libraries have different default settings.

From SET QUOTED_IDENTIFIER:

The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties. The default for SET QUOTED_IDENTIFIER is OFF for connections from DB-Library applications.

If you're not happy accepting the defaults, or (as here) you want to be able to run scripts predictably no matter what defaults are used, explicitly set the options that you need.

---

Also, note specifically that QUOTED_IDENTIFIER and ANSI_NULLS are captured when defining modules (e.g. triggers, stored procs, etc). You want to make sure those settings are applied on your connection, outside of any specific module definitions.

It's a good (perhaps best) practice to always specify the -I command-line argument when executing SQLCMD so that QUOTED_IDENTIFIER is on by default. The setting is off by default in SQLCMD for backwards compatibility.