构建数据库项目时出现“不正确的 SET 选项"错误 [英] 'Incorrect SET Options' Error When Building Database Project

问题描述

我们正在使用 Visual Studio 和一个数据库项目来生成我们的数据库.

We are using Visual Studio and a database project to generate our database.

我刚刚进行了一些数据库更改(包括添加一个名为 Correspondence 的新表)将这些更改导入到数据库项目中，并尝试部署(重建)数据库.

I just made a number of database changes (including adding a new table named Correspondence) imported those changes into the database project, and attempted to deploy (rebuild) the database.

当我这样做时，我收到以下错误:

When I do, I get the following error:

正在创建 [dbo].[通信]...消息 1934，级别 16，状态 1，服务器(服务器名称)，第 1 行CREATE TABLE 失败，因为以下 SET 选项的设置不正确: 'ANSI_WARNINGS, ANSI_PADDING'.验证 SET 选项是否可以正确使用在计算列和/或过滤索引上带有索引视图和/或索引和/或查询通知和/或 XML 数据类型方法和/或空间索引操作.

Creating [dbo].[Correspondence]... Msg 1934, Level 16, State 1, Server (Server Name), Line 1 CREATE TABLE failed because the following SET options have incorrect settings : 'ANSI_WARNINGS, ANSI_PADDING'. 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.

谁能向我解释这个错误，并帮助我解决它?这是数据库项目用于创建此表的脚本.

Can anyone explain this error to me, and help me resolve it? Here's the script the database project uses to create this table.

PRINT N'Creating [dbo].[Correspondence]...';
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO

CREATE TABLE [dbo].[Correspondence] (
    [Id]                INT              IDENTITY (1, 1) NOT NULL,
    [WorkbookId]        INT              NOT NULL,
    [ProviderId]        UNIQUEIDENTIFIER NOT NULL,
    [MessageThreadId]   INT              NOT NULL,
    [MessageThreadType] AS               ((1)) PERSISTED NOT NULL
);
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
GO

PRINT N'Creating PK_Correspondence...';
GO

ALTER TABLE [dbo].[Correspondence]
ADD CONSTRAINT [PK_Correspondence] PRIMARY KEY CLUSTERED ([Id] ASC)
    WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF,
    IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO

推荐答案

根据 提单:

计算列上的索引视图和索引将结果存储在数据库供以后参考.存储的结果只有在所有的情况下才有效引用索引视图或索引计算列的连接可以生成与创建连接的连接相同的结果集索引.

Indexed views and indexes on computed columns store results in the database for later reference. The stored results are valid only if all connections referring to the indexed view or indexed computed column can generate the same result set as the connection that created the index.

为了创建一个带有持久化计算列的表，必须启用以下连接设置:

In order to create a table with a persisted, computed column, the following connection settings must be enabled:

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT ON
SET QUOTED_IDENTIFIER ON

这些值是在数据库级别设置的，可以使用以下方式查看:

These values are set on the database level and can be viewed using:

SELECT 
    is_ansi_nulls_on,
    is_ansi_padding_on,
    is_ansi_warnings_on,
    is_arithabort_on,
    is_concat_null_yields_null_on,
    is_numeric_roundabort_on,
    is_quoted_identifier_on
FROM sys.databases

然而，SET 选项也可以由连接到 SQL Server 的客户端应用程序设置.

一个完美的例子是 SQL Server Management Studio，它的 SET ANSI_NULLS 和 SET QUOTED_IDENTIFIER 的默认值都为 ON.这是我最初无法复制您发布的错误的原因之一.

A perfect example is SQL Server Management Studio which has the default values for SET ANSI_NULLS and SET QUOTED_IDENTIFIER both to ON. This is one of the reasons why I could not initially duplicate the error you posted.

无论如何，要复制错误，请尝试此操作(这将覆盖 SSMS 默认设置):

Anyway, to duplicate the error, try this (this will override the SSMS default settings):

SET ANSI_NULLS ON
SET ANSI_PADDING OFF
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON 
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE T1 (
    ID INT NOT NULL,
    TypeVal AS ((1)) PERSISTED NOT NULL
) 

您可以使用以下方法修复上面的测试用例:

You can fix the test case above by using:

SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

我建议在创建表和相关索引之前在脚本中调整这两个设置.

I would recommend tweaking these two settings in your script before the creation of the table and related indexes.

这篇关于构建数据库项目时出现“不正确的 SET 选项"错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！