Menu
Menu
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.
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.
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.
| QUOTEDIDENTIFIER |
| 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
| QUOTEDIDENTIFIER |
| OFF |
On yet another server: 14.0.3015.40
| QUOTEDIDENTIFIER |
| OFF |
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).
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
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.
