`bcp in` fails with "INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'"

Question

I exported a table (created with EF Core 2.2) with bcp %database%.MyTable out MyTable.dmp -n -T -S %sqlserver%.

On reimporting it with bcp %database%.MyTable in MyTable.dmp -n -T -S %sqlserver% I get this error:

SQLState = 37000, NativeError = 1934
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]INSERT 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.

The table is created with QUOTED_IDENTIFIER = ON and does not contain any computed column:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [MyTable](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [OwnerLoginId] [bigint] NOT NULL,
    [RowVersion] [timestamp] NULL,
    [CreatedAt] [datetime2](7) NOT NULL,
    [DataId] [bigint] NOT NULL,
    [SomeString] [nvarchar](30) NOT NULL,
    [AnotherString] [nvarchar](30) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [MyTable]  WITH CHECK ADD  CONSTRAINT [FK_MyTable_SomeOtherTable_DataId_SomeString] FOREIGN KEY([DataId], [SomeString])
REFERENCES [SomeOtherTable] ([DataId], [SomeString])
GO

ALTER TABLE [MyTable] CHECK CONSTRAINT [FK_MyTable_SomeOtherTable_DataId_SomeString]
GO

ALTER TABLE [MyTable]  WITH CHECK ADD  CONSTRAINT [FK_MyTable_DataTable_DataId] FOREIGN KEY([DataId])
REFERENCES [DataTable] ([Id])
GO

ALTER TABLE [MyTable] CHECK CONSTRAINT [FK_MyTable_DataTable_DataId]
GO

Answer 1

The BCP utility connects with QUOTED_IDENTIFIER OFF for backwards compatibility. You'll need to add the -q option to use QUOTED_IDENTIFIER ON.

The BCP documentation instructs the entire qualified table name be enclosed in quotes when the -q option is specified. However, this does not work when the database name doesn't conform to regular identifier naming rules. The period in the database name is the culprit here.

A workaround is to specify a 2-part table name and specify the database name separately using the -d option:

bcp "MySchema.MyTable" in "MyTable.dmp" -q -n -T -S "(localdb)\MSSQLLocalDB" -d "My.Database"

IMHO, it is best to name objects according to the rules for regular identifiers to avoid the need to enclose the names and jumping through hoops like this.

The error message suggests you are using other features besides indexes on computed columns that require QUOTED_IDENTIFIER ON. These include filtered indexes, indexed views, and XML indexes. My guess is a filtered index or indexed view is the culprit here.