CODEWITHSUNDEEP
sqlsql-server
AndroidAL
AndroidAL

Reputation: 1119

Schedule SQL query ERROR -Set QUOTED IDENTIFIER

I have tried to schedule an sql query it failed,and the error that I got was,

Update failed because the following SET 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.[SQLSTATE 42000](Error 1934).

I have this solution

Add SET QUOTED_IDENTIFIER ON
before your UPDATE statements in SQL Job step.

But, what does SET QUOTED_IDENTIFIER ON do ? Could someone explain it to me?

Thanks

EDIT 1; I tried the above , the scheduled query executed , but did not change an of the fields anything?

The Query is use;

SET QUOTED_IDENTIFIER ON
update dbo.myTable
set name=[dbo].[functName](name);

If I run a normal UPDATE query ,

update dbo.myTable set name=[dbo].[functName](name);

It works, and fields are changed.

Why would it not work in the scheduled query?

Thanks

Upvotes: 0

Views: 484

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172528

It specifies how SQL Server will be treating your data which you have defined in Single Quotes and Double Quotes. The MSDN gives you the answer:

Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers.

Also check the Remarks section which provides the details about ON and OFF.

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. For more information, see Database Identifiers. Literals can be delimited by either single or double quotation marks................

Upvotes: 1

Related Questions