主页 / dba / 问题 / 52802
Accepted
Kahn
Asked: 2013-11-06 02:38:35 +0800 CST

SQL Server 代理：QUOTED_IDENTIFIER

  • 772

场景相当简单：作业在生产中失败，发出错误“ALTER INDEX 失败，因为以下 SET 选项的设置不正确：'QUOTED_IDENTIFIER' ...”。

问题是，为什么这个问题会出现在生产环境中，而不是出现在我们的任何测试环境中？检查 DBCC USEROPTIONS，结果在所有数据库中完全相同。此外，脚本本身根本不包含引号。它只是重建一些索引并使用全扫描更新一些统计信息。

在任何其他数据库上使用完全相同的作业，我可以将 QUOTED_IDENTIFIER 设置为 ON 或 OFF，或者根本不设置它并让它在连接默认值上运行。无论如何，它总是有效的。但在生产中，它似乎必须设置为 ON 或 OFF。

我问这个是因为时间方面我负担不起每天测试一次，我需要知道为什么会发生这个错误以及如何预测需要哪个设置以及何时需要。测试和生产数据库之间唯一明显的区别是排序规则，但即使创建测试数据库以匹配生产中的排序规则，无论 QUOTED_IDENTIFIER 设置如何，作业仍然有效。

谢谢！

sql-server configuration

3 个回答

  1. Best Answer

    我刚刚遇到了同样的问题。默认情况下，作业步骤中的第一条语句是 to SET QUOTED_IDENTIFIER OFF- 您可以在 Profiler 跟踪中看到这一点。解决方案是在代码的开头添加一行到SET QUOTED_IDENTIFIER ON.

    至于您的脚本不包含引号 - 我相信在创建或重建某些类型的索引时，需要进行设置。

    这里有几个链接：

    https://stackoverflow.com/questions/15218893/first-statement-issued-by-sql-server-agent-sets-quoted-identifier-off

    • 2

  2. 我发现 SQL 代理作业根据数据库的兼容性级别设置数据库选项，这可以覆盖已设置的数据库选项。

    即，数据库具有 SET QUOTED_IDENTIFIER ON 的数据库选项，但是数据库的兼容性级别设置为 SQL 2000，因此 SQL 作业失败（即，当尝试更新已过滤索引的表中的行时）。

    将兼容级别切换到 SQL 2008，然后 SQL 作业将成功。

    • 2

  3. 我在安排创建索引的作业时遇到了同样的问题。我所做的是

    1) - 我运行这个脚本来确定选项的当前值是什么

    SELECT  *
FROM
(
    SELECT  name,
        [Status] = CASE WHEN (@@OPTIONS & number) = 0 THEN 'OFF' ELSE 'ON' END
    FROM    master.dbo.spt_values 
    WHERE       type='SOP'
        AND number > 0
) AS X

    2）我修改了我的创建索引作业，根据需要添加了 QUOTED_IDENTIFIER 的设置。您可以在下面的工作创建代码中看到这一点。这目前正在工作。

    --------------------------------------------------
-- server is SQLBOCSSLON1

-- this script creates an AD HOC job each step is a missing index in improving performance of 
--'spCustomerSearchBySurnameRaw'
--'spCustomerSearchByFirstNameAndSurnameRaw'

-- this file will be located at:
--B:\Projects\2015\SQLBOCSSLON1\SP Tuning\TEST\spCustomerSearchBySurnameRaw\20150529

--Marcelo Miorelli
--29-May-2015
--------------------------------------------------

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Ad Hoc - Create new Indexes - Bocss2', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBAddress_blnIsCurrent_strDeliveryPoint_INC_lngAddressID]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED INDEX [IDX_tblBAddress_blnIsCurrent_strDeliveryPoint_INC_lngAddressID]', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBAddress_blnIsCurrent_strDeliveryPoint_INC_lngAddressID]
ON [dbo].[tblBAddress] 
([blnIsCurrent], [strDeliveryPointSuffix], [strCountryCode], [strPostalCodeLookup]) 
INCLUDE (lngAddressID) WITH (ONLINE = ON)', 
        @database_name=N'Bocss2', 
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBAddress_blnIsCurrent_INC_lngAddressID_strAccountCode]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED INDEX [IDX_tblBAddress_blnIsCurrent_INC_lngAddressID_strAccountCode]', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBAddress_blnIsCurrent_INC_lngAddressID_strAccountCode] 
ON [dbo].[tblBAddress] ([blnIsCurrent]) 
INCLUDE (lngAddressID,strAccountCode) WITH (ONLINE = ON)', 
        @database_name=N'Bocss2', 
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBColdList_sintMarketID_strAddressLine3_INC_strAccountCode_strTitle_strFirstNames]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED [INDEX IDX_tblBColdList_sintMarketID_strAddressLine3_INC_strAccountCode_strTitle_strFirstNames]', 
        @step_id=3, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBColdList_sintMarketID_strAddressLine3_INC_strAccountCode_strTitle_strFirstNames] 
ON [dbo].[tblBColdList] 
([sintMarketID], [strAddressLine3], [sdtmCopiedToAccount], [blnUsable], [strSurname], [strPostalCodeLookup]) 
INCLUDE (strAccountCode,strTitle,strFirstNames) WITH (ONLINE = ON)

', 
        @database_name=N'Bocss2', 
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBColdList_strFirstNames_blnUsablestrSurname_INC_strAccountCode_sintMarketID]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED INDEX [IDX_tblBColdList_strFirstNames_blnUsablestrSurname_INC_strAccountCode_sintMarketID]', 
        @step_id=4, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBColdList_strFirstNames_blnUsablestrSurname_INC_strAccountCode_sintMarketID]
ON [dbo].[tblBColdList] ([strFirstNames], [blnUsable], [strSurname]) INCLUDE (strAccountCode,sintMarketID,strTitle,strAddressLine1,strAddressLine2,strAddressLine3,strAddressLine4,strAddressLine5,strAddressLine6,strPostalCode,strCountryCode,strEmail) WITH (ONLINE = ON)
', 
        @database_name=N'Bocss2', 
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBColdList_strFirstNames_blnUsable_INC_strAccountCode_sintMarketID]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED [INDEX IDX_tblBColdList_strFirstNames_blnUsable_INC_strAccountCode_sintMarketID]', 
        @step_id=5, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBColdList_strFirstNames_blnUsable_INC_strAccountCode_sintMarketID]
ON [dbo].[tblBColdList] ([strFirstNames], [blnUsable]) INCLUDE (strAccountCode,sintMarketID,strTitle,strSurname,strAddressLine1,strAddressLine2,strAddressLine3,strAddressLine4,strAddressLine5,strAddressLine6,strPostalCode,strCountryCode,strEmail) WITH (ONLINE = ON)
', 
        @database_name=N'Bocss2', 
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CREATE NONCLUSTERED INDEX IDX_tblBColdList_strFirstNamesstrPostalCodeLookup_INC_strAccountCode]    Script Date: 29/05/2015 15:39:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CREATE NONCLUSTERED INDEX [IDX_tblBColdList_strFirstNamesstrPostalCodeLookup_INC_strAccountCode]', 
        @step_id=6, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SET QUOTED_IDENTIFIER ON; CREATE NONCLUSTERED INDEX [IDX_tblBColdList_strFirstNamesstrPostalCodeLookup_INC_strAccountCode] 
ON [dbo].[tblBColdList] ([strFirstNames], [strPostalCodeLookup]) INCLUDE (strAccountCode) WITH (ONLINE = ON)

', 
        @database_name=N'Bocss2', 
        @flags=20
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO
    • 1

相关问题