"bcp in"失败，并显示"INSERT failed，因为以下SET选项的设置不正确:'QUOTED_IDENTIFIER'" [英] `bcp in` fails with "INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'"

问题描述

我用 bcp％database％.MyTable导出了一个表(使用EF Core 2.2创建)，并将MyTable导出为MyTable.dmp -n -T -S％sqlserver％.

在使用 bcp％database％.MyTable.dmp中的MyTable重新导入它-n -T -S％sqlserver％时出现此错误:

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.

该表是使用 QUOTED_IDENTIFIER = ON 创建的，并且不包含任何

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

推荐答案

BCP实用程序与 QUOTED_IDENTIFIER OFF 连接以实现向后兼容.您需要添加 -q 选项以使用 QUOTED_IDENTIFIER ON .

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

当指定了 -q 选项时，BCP文档指示整个合格的表名都用引号引起来.但是，如果数据库名称不符合

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.

一种解决方法是指定一个由两部分组成的表名，并使用 -d 选项分别指定数据库名称:

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.

该错误消息建议您使用除需要 QUOTED_IDENTIFIER ON 的计算列上的索引以外的其他功能.这些包括过滤索引，索引视图和XML索引.我的猜测是这里的过滤索引或索引视图是元凶.

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.

这篇关于"bcp in"失败，并显示"INSERT failed，因为以下SET选项的设置不正确:'QUOTED_IDENTIFIER'"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！