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.
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.