“设置选项不正确”构建数据库项目时出错

发布于 2025-01-04 11:42:06 字数 1205 浏览 0 评论 0原文

我们使用 Visual Studio 和数据库项目来生成数据库。

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

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

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

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

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

原文

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

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:

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

酷到爆炸 2025-01-11 11:42:06

根据 BOL：

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

为了创建具有持久计算列的表，必须启用以下连接设置：

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

这些值在数据库级别设置，并且可以使用以下方式查看：

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。这是我最初无法复制您发布的错误的原因之一。

无论如何，要重复错误，请尝试以下操作（这将覆盖 SSMS 默认设置）：

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
)

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

SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

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

According to BOL:

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

However, the SET options can also be set by the client application connecting to SQL Server.

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.

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.

回复收藏 0 原文

单身狗的梦 2025-01-11 11:42:06

我找到了此问题的解决方案：

转到服务器属性。
选择“连接”选项卡。
检查 ansi_padding 选项是否未选中。

回复收藏 0 原文

请止步禁区 2025-01-11 11:42:06

就我而言，我尝试在 MS SQL Server 2012 上从一个数据库到另一个数据库创建一张表。右键单击一个表并选择 Script Table as >删除并创建到>新的查询编辑器窗口，创建了以下脚本：

USE [SAMPLECOMPANY]
GO

ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [FK_Employees_Departments]
GO

/****** Object:  Table [dbo].[Employees]    Script Date: 8/24/2016 9:31:15 PM ******/
DROP TABLE [dbo].[Employees]
GO

/****** Object:  Table [dbo].[Employees]    Script Date: 8/24/2016 9:31:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Employees](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentId] [int] NOT NULL,
    [FullName] [varchar](50) NOT NULL,
    [HireDate] [datetime] NULL
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
    [EmployeeId] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Departments] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Departments] ([DepartmentID])
GO

ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Departments]
GO

但是，在执行上述脚本时，它返回错误：

SELECT 失败，因为以下 SET 选项不正确
设置：“ANSI_PADDING”。验证 SET 选项的使用是否正确
带有索引视图和/或计算列上的索引和/或过滤
索引和/或查询通知和/或 XML 数据类型方法和/或
空间索引操作。

我找到的解决方案：启用脚本顶部的设置，如下所示：

USE [SAMPLECOMPANY]
GO
/****** Object:  Table [dbo].[Employees]    Script Date: 8/24/2016 9:31:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [FK_Employees_Departments]
GO

/****** Object:  Table [dbo].[Employees]    Script Date: 8/24/2016 9:31:15 PM ******/
DROP TABLE [dbo].[Employees]
GO



CREATE TABLE [dbo].[Employees](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentId] [int] NOT NULL,
    [FullName] [varchar](50) NOT NULL,
    [HireDate] [datetime] NULL
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
    [EmployeeId] 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 [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Departments] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Departments] ([DepartmentID])
GO

ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Departments]
GO

SET ANSI_PADDING OFF
GO

希望有帮助。

In my case I was trying to create a table from one database to another on MS SQL Server 2012. Right-clicking on a table and selecting Script Table as > DROP And CREATE To > New Query Editor Window, following script was created:

USE [SAMPLECOMPANY]
GO

ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [FK_Employees_Departments]
GO

/****** Object:  Table [dbo].[Employees]    Script Date: 8/24/2016 9:31:15 PM ******/
DROP TABLE [dbo].[Employees]
GO

/****** Object:  Table [dbo].[Employees]    Script Date: 8/24/2016 9:31:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Employees](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentId] [int] NOT NULL,
    [FullName] [varchar](50) NOT NULL,
    [HireDate] [datetime] NULL
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
    [EmployeeId] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Departments] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Departments] ([DepartmentID])
GO

ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Departments]
GO

However when executing above script it was returning the error:

SELECT failed because the following SET options have incorrect
settings: '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.

The Solution I've found: Enabling the settings on the Top of the script like this:

USE [SAMPLECOMPANY]
GO
/****** Object:  Table [dbo].[Employees]    Script Date: 8/24/2016 9:31:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [FK_Employees_Departments]
GO

/****** Object:  Table [dbo].[Employees]    Script Date: 8/24/2016 9:31:15 PM ******/
DROP TABLE [dbo].[Employees]
GO



CREATE TABLE [dbo].[Employees](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentId] [int] NOT NULL,
    [FullName] [varchar](50) NOT NULL,
    [HireDate] [datetime] NULL
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
    [EmployeeId] 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 [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Departments] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Departments] ([DepartmentID])
GO

ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Departments]
GO

SET ANSI_PADDING OFF
GO

Hope this help.

回复收藏 0 原文

黑寡妇 2025-01-11 11:42:06

对我来说，只需将兼容性级别设置为更高级别就可以了。查看 C.Level ：

select compatibility_level from sys.databases where name = [your_database]

For me, just setting the compatibility level to higher level works fine. To see C.Level :

select compatibility_level from sys.databases where name = [your_database]

回复收藏 0 原文

浊酒尽余欢 2025-01-11 11:42:06

就我而言，我发现计算列已添加到索引的“包含列”中。后来，当更新该表中的某个项目时，merge 语句失败并显示该消息。合并是在触发器中进行的，因此很难追踪！从索引中删除计算列修复了它。

回复收藏 0 原文

偏闹i 2025-01-11 11:42:06

我对过滤索引有同样的问题，并且我的插入和更新失败。我所做的只是将具有插入和更新语句的存储过程更改为：

create procedure abc
()
AS
BEGIN
SET NOCOUNT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON 
SET ANSI_WARNINGS ON 
SET ANSI_PADDING ON 
end

I had the same issue with the filtered index and my inserts and updates were failing. All I did was to change the stored procedure that had the insert and update statement to:

create procedure abc
()
AS
BEGIN
SET NOCOUNT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON 
SET ANSI_WARNINGS ON 
SET ANSI_PADDING ON 
end

回复收藏 0 原文