Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The scenario is rather simple: A Job fails in production, giving off an error "ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' ...".

The question is, why does this problem appear in production, and not in any of our testing environments? Checking DBCC USEROPTIONS, the results are exactly the same across all databases. Also, the script itself contains no quotes at all. It simply rebuilds a few indexes and updates a few statistics with fullscan.

Using the exact same job on any other database, I can set QUOTED_IDENTIFIER ON, or OFF, or just not set it at all and let it run on connection defaults. And it'll always work regardless. But in production, it appears it either has to be set ON or OFF.

I'm asking this because time-wise I can't afford to test this once a day, I need to know why this error is happening and how to predict which setting is needed and when. The only visible difference between the test and production databases is collation, but even creating a testing database to match the collation in production, the Job still works regardless of the QUOTED_IDENTIFIER setting.

Thanks!

share|improve this question
    
"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." Source. Does the job use any procedures? If so does sys.sql_modules show the same setting on all servers? –  Martin Smith Nov 5 '13 at 11:07
    
Nope, the job is literally one line of comments /* comment */ followed by a number of straight, hard-coded index rebuilds and stats updates. It doesn't refer to any procedures, nor has any dynamic or parameterized components of any kind. –  Kahn Nov 5 '13 at 11:16

3 Answers 3

I just ran into this same issue. By default the first statement in a job step is to SET QUOTED_IDENTIFIER OFF - you can see this in a Profiler trace. The solution is to add a line at the beginning of your code to SET QUOTED_IDENTIFIER ON.

As to your script not containing quotes - I believe when creating or rebuilding certain types of indexes that setting is required is to be on.

Here's a couple links:

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

http://blogs.msdn.com/b/sqlagent/archive/2010/11/05/sql-agent-issue-with-identifiers-delimited-by-double-quotation-marks.aspx

share|improve this answer

I discovered that the SQL Agent job sets the database options based on the compatibility level of the database and this can override the database options that have been set.

i.e. Database has database options of SET QUOTED_IDENTIFIER ON however the compatibiliy level of the database is set to SQL 2000 so that the SQL Job fails (i.e. when attempting to UPDATE rows in table that has filtered index on it).

Switch the compatibility level to SQL 2008 and then the SQL job will succeed.

share|improve this answer

I run into the same issue while schedule a job to create indexes. what I did is

1) - I run this script to identity what is the current value of the options

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) I modified my create index job, adding the setting of QUOTED_IDENTIFIER as required. You can see this on the job creation code below. this is currently working.

--------------------------------------------------
-- 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.