更新失败，因为以下 SET 选项的设置不正确:'QUOTED_IDENTIFIER' [英] UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

问题描述

我在更新存储过程时遇到问题.错误是:

I am having a problem with an update stored procedure. The error is:

更新失败，因为以下 SET 选项的设置不正确:QUOTED_IDENTIFIER".验证 SET 选项是否适用于计算列上的索引视图和/或索引和/或查询通知和/或 xml 数据类型方法.

UPDATE 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 query notifications and/or xml data type methods.

SQL 状态:42000
本机错误:1934

SQL State: 42000
Native Error: 1934

遗憾的是，此表没有索引视图、计算列或查询通知.这个存储过程在过去几天运行良好，从今天开始一直返回这个错误.

Unfortunately, there are no indexed views, computed columns, or query notifications for this table. This Stored Procedure was running fine for past couple of days and since today has been returning this error.

有什么建议可以帮助确定问题吗?

Is there any suggestion that would help in identifying the problem?

注意:如果我将 quoted_identifier 设置为 ON 并重新运行 CREATE PROCEDURE，问题将是固定(现在).但我想首先了解是什么触发了这个问题.

Note: If I set the quoted_identifier to ON and rerun the CREATE PROCEDURE, the issue will be fixed (for now). But I want to understand what triggered this issue in the first place.

推荐答案

我们无法通过将引用标识符设置为关闭来创建索引视图.我刚刚尝试过，如果 SQL 2005 关闭，它会立即抛出错误:

We cannot create a indexed view by setting the quoted identifier off. I just tried it and SQL 2005 throws an error straight away if it is turned off:

无法创建索引.对象SmartListVW"是在关闭以下 SET 选项的情况下创建的:QUOTED_IDENTIFIER".

Cannot create index. Object 'SmartListVW' was created with the following SET options off: 'QUOTED_IDENTIFIER'.

正如 gbn 所说，重建索引必须是它被关闭的唯一其他方式.

As gbn said, rebuilding the indexes must be the only other way it got turned off.

我看到很多文章都说在为视图创建索引之前必须打开它.否则在插入、更新表时会出现错误，但在这里我可以立即得到错误，因此 sql 引擎不允许通过将其设置为关闭来在视图上创建索引，根据这个 msdn 链接.

I have seen lots of articles saying it must be on before creating index on views. Otherwise you would get an error while inserting, updating the table, but here I can get the error straight away, so sql engine won't allow to create index on views by setting it to off, per this msdn link.

我曾经在这里在堆栈中问过一个类似的问题...

I have asked a similar question here in stack sometime ago...

编辑

我关闭了全局查询执行(在编辑器中)ANSI 设置并在新编辑器中运行索引脚本，这一次它也抛出了同样的错误.所以很明显我们不能通过关闭quoted_identifier来为视图创建索引.

I turned off the global queryexecution (in editor) ANSI settings and ran the index script in new editor, this time also it throws the same error. So it's clear we can't create indexes on views by turning off quoted_identifier.

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