When the SQL Server Agent overrides default behavior: QUOTED_IDENTIFIER

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.

Specs

• From SQL Server 2012  (11.0.7001)
• To SQL Server 2017 CU 1 (14.0.1000)
• Windows Server 2012 R2

Query Execution

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.”

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql

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.

Testing

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;  

QUOTED_IDENTIFIER
ON

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

QUOTEDIDENTIFIER
OFF

A simple approach to resolve this was ofcourse to set it to ON.

More Testing

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.

The Culprit

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).

Closer look:

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.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql

Conclusion

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.