September 12, 2016 at 11:46 am
We were debugging a production issue where a new SQL Agent job threw the following error:
INSERT 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. [SQLSTATE 42000] (Error 1934). The step failed.
We do have an indexed view on one of the tables being updated, and also our script worked when run directly from Management Studio, so a bit of research brought one of our team members to this post on Stack Overflow: http://stackoverflow.com/questions/15218893/first-statement-issued-by-sql-server-agent-sets-quoted-identifier-off
So, we added SET QUOTED_IDENTIFIER ON; as the first line of the T-SQL job step, and that fixed the problem. By the way, I did verify that Quoted Identifier was enabled for the database in question.
This brings up two questions:
1. Is this really true? Does the SQL Agent really set QUOTED_IDENTIFIER OFF; for T-SQL job steps? I did not find any mention of this in the MSDN article for QUOTED_IDENTIFIER: https://msdn.microsoft.com/en-us/library/ms174393.aspx
2. Why would this be the default for SQL Agent? I've been cautioned always against using non-default ANSI settings without a compelling reason. (Laziness does not count as a "compelling" reason.) What was the rationale behind this design decision?
If anyone knows, I would be grateful for your insight. 🙂
September 12, 2016 at 12:37 pm
1. Is this really true? Does the SQL Agent really set QUOTED_IDENTIFIER OFF; for T-SQL job steps? I did not find any mention of this in the MSDN article for QUOTED_IDENTIFIER: https://msdn.microsoft.com/en-us/library/ms174393.aspx
2. Why would this be the default for SQL Agent? I've been cautioned always against using non-default ANSI settings without a compelling reason. (Laziness does not count as a "compelling" reason.) What was the rationale behind this design decision?
1. Yes it is really true. I think it's the same behavior for sqlcmd, bcp, osql...is isql even available anymore?
2. You would likely need to check with the Sybase group who originally developed that piece. I think it's still the default for all of ASE
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply