For a customer, I recently migrated one of their databases from 2012 to 2017, whilst putting compression on all the indexes. The process of recreating the Indexes on 8 new filegroups took 3 hours for a 400GB database.
- From SQL Server 2012 (11.0.7001)
- To SQL Server 2017 CU 1 (14.0.1000)
- Windows Server 2012 R2
When manually executing the queries, life was bliss. When putting the queries needed to complete the task in a job for automation purposes, issues arose:
When I checked the captured errors in a log table used in the queries, I was greeted with this message, which was not present when I manually ran the queries:
“CREATE 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.”
From Microsoft docs:
“When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers…”
“SET QUOTED_IDENTIFIER must be ON when you are creating a filtered index.”
I know, It was my mistake to put the query directly into the job step, but it was a test run, I and wanted to be quick.
Since I had filtered indexes, QUOTED_IDENTIFIER needed to be ON at all times when executing the query’s.
If I execute the following statement under my account (sysadmin) on the database to be compressed.
DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF'; IF ( (256 & @@OPTIONS) = 256 ) SET @QUOTED_IDENTIFIER = 'ON'; SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER;
When I put the query to check the quoted identifier in a job and run it, (in the user database to be compressed) this happens:
create table dbo.QUOTEDIDENTIFIER_CHECK(QUOTEDIDENTIFIER varchar(3)) DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF'; IF ( (256 & @@OPTIONS) = 256 ) SET @QUOTED_IDENTIFIER = 'ON'; SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER; insert into dbo.QUOTEDIDENTIFIER_CHECK(QUOTEDIDENTIFIER) select @QUOTED_IDENTIFIER
select * from dbo.QUOTEDIDENTIFIER_CHECK
A simple approach to resolve this was ofcourse to set it to ON.
And that is what I did, added SET QUOTED_IDENTIFIER ON; to the executed statements.
But, I also wanted to know what causes this.
When tested with a job on a different server :11.0.7001.0
On yet another server: 14.0.3015.40
After playing around with the connections at server and database level, I found what caused this.
When checking the profiler when executing the job, I found that it gets explicitly set to off before the step execution. Under the SQLSERVERAGENT service account (also not best practice but it illustrates the point better).
Executed by the agent:
Seems that this is default behavior for the Agent service. For now, I have not found a better way than explicitly setting quoted identifier on .
The reason behind this behaviour, is something that I am still looking for, since it is not documented in the Microsoft docs on quoted_identifier.
The main thing to keep in mind, is that when adapting indexes in a t-sql job, (without using a stored procedure), your filtered index creation will fail. Your strings might also get interpreted differently. Turning this on by using SET QUOTED_IDENTIFIER ON in your job step will fix this.