kohera-logo-regular.svg

When the SQL Server Agent overrides default behavior: QUOTED_IDENTIFIER

Vector_BG.png

When the SQL Server Agent overrides default behavior: QUOTED_IDENTIFIER

Vector_BG.png

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.

 

QUOTEDIDENTIFIER
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

 

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.

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.


2319-blog-database-specific-security-featured-image
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
kohera-2312-blog-sql-server-level-security-featured-image
SQL Server security on server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
blog-security_1
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...
DSC_1388
Aggregaties
Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
blog-2303
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...