从 sp_msForEachTable 运行时，由于 QUOTED_IDENTIFIER ALTER INDEX 失败 [英] ALTER INDEX failed because of QUOTED_IDENTIFIER when running from sp_msForEachTable

问题描述

当我尝试在表上重建索引时:

When I try to rebuild an index on a table:

ALTER INDEX ALL ON [dbo].[Allocations] REBUILD

效果很好.

但是当我打电话

EXECUTE sp_msForEachTable 'ALTER INDEX ALL ON ? REBUILD'

我到达同一张桌子，但失败了:

I reach the same same table, and it fails with:

消息 1934，级别 16，状态 1，第 2 行
ALTER INDEX 失败，因为以下 SET 选项的设置不正确:'QUOTED_IDENTIFIER'.验证 SET 选项是否适用于计算列上的索引视图和/或索引和/或过滤索引和/或查询通知和/或 XML 数据类型方法和/或空间索引操作.

Msg 1934, Level 16, State 1, Line 2
ALTER INDEX 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.

<小时>

并确认它是同一张表:

---

And to confirm that it's the same table:

EXECUTE sp_msForEachTable 'print ''Rebuilding ?'';
ALTER INDEX ALL ON ? REBUILD;
PRINT ''   Done ?'''

给出结果:

Rebuilding [dbo].[SystemConfiguration]
   Done [dbo].[SystemConfiguration]
Rebuilding [dbo].[UserGroups]
   Done [dbo].[UserGroups]
Rebuilding [dbo].[Groups]
   Done [dbo].[Groups]
Rebuilding [dbo].[UserPermissions]
   Done [dbo].[UserPermissions]
Rebuilding [dbo].[AllocationAdmins]
   Done [dbo].[AllocationAdmins]
Rebuilding [dbo].[Allocations]
Msg 1934, Level 16, State 1, Line 2
ALTER INDEX 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.

我没有做错什么?

注意:

EXECUTE sp_msForEachTable 'DBCC DBREINDEX(''?'')' 

工作很好！

works fine!

推荐答案

带引号的标识符设置针对每个存储过程存储，并且 sp_MSforeachtable 将其定义为 OFF.但是，您可以解决此问题 - 在执行重新索引之前将其设置为 ON:

Quoted identifier settings are stored against each stored procedure, and sp_MSforeachtable has it defined as OFF. However, you can work around this - by setting it to ON before it executes the re-index:

create table dbo.T (
    ID int not null,
    constraint PK_T PRIMARY KEY (ID)
)
go
create view dbo.V ( ID)
with schemabinding
as
    select ID from dbo.T
go
create unique clustered index IX_V on dbo.V(ID)
go
ALTER INDEX ALL ON dbo.V REBUILD --Fine
go
exec sp_MSforeachtable  'ALTER INDEX ALL ON ? REBUILD' --Errors
go
exec sp_MSforeachtable  'SET QUOTED_IDENTIFIER ON;
ALTER INDEX ALL ON ? REBUILD' --Fine

<小时>

SET QUOTED_IDENTIFIER:

创建存储过程时，SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 设置将被捕获并用于该存储过程的后续调用.

When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.

<小时>

当然，插入关于 sp_MSforeachtable 未记录的通常警告，因此您不能依赖其任何行为的稳定.

---

And, of course, insert the usual caveats about sp_MSforeachtable being undocumented, and so you can't rely on any of its behaviour being stable.

对于 DBCC DBREINDEX - 所有赌注都已关闭.DBCC 生活在它自己的小而非常定制的代码世界中.但是，当然，未来的工作也不应该依赖它:

For DBCC DBREINDEX - all bets are off. DBCC lives in its own little, very customized code world. But, of course, it shouldn't be relied on for future work either:

此功能将在 Microsoft SQL Server 的未来版本中删除.请勿在新的开发工作中使用此功能，并尽快修改当前使用此功能的应用程序.改用 ALTER INDEX.

This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER INDEX instead.

这篇关于从 sp_msForEachTable 运行时，由于 QUOTED_IDENTIFIER ALTER INDEX 失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！