April 26, 2017 at 6:50 am
Hello,
The following part of the SQL code works perfectly well from SSMS, but fails when scheduled (SQL Job).
SET QUOTED_IDENTIFIER is set to On and then Off after the Exec of the command.
The Error Message is below:
SET QUOTED_IDENTIFIER ON
Declare @command varchar (max)
SELECT @command = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ';' + 'EXEC sp_defaultlanguage @loginame='''
+ name
+ ''',@language=''us_english'''
FROM sys.server_principals
....
Message
Executed as user: NT AUTHORITY\SYSTEM. SELECT 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.
Any thoughts, why would it not work from the SQL jobs?
Thank you,
Vin
April 26, 2017 at 7:03 am
you are declaring a command and executing it. the internal command does not have the same settings for quoted identifier
Declare @command varchar (max)
SELECT @command = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ';' + 'SET QUOTED IDENTIFIER ON; EXEC sp_defaultlanguage @loginame='''
+ name
+ ''',@language=''us_english'''
FROM sys.server_principals
Lowell
April 26, 2017 at 7:10 am
Hi Lowell,
After the change, I am getting the same error. I removed Set command before Declare too.
Vin
April 26, 2017 at 7:32 am
you did not show your entire query, so it's hard to diagnose.
review your code, because each and every EXECUTE(@SomeCommand) could be the potential problem. how many executes do you have? maybe the first works,and the second doesn't?
I use this to determine my settings all the time; I've seen situations where an OleDB connection from an App or from Powershell does not have the same settings that are defaulted in my SSMS.
you might want to run this command inside your SQLjob, but have it insert into some quick and dirty audit table, or raise an error, or something so you can see what the settings are inside the job, that are different from your own SSMS sessions in your executed code.SELECT ' SET ANSI_NULLS ' + CASE WHEN SESSIONPROPERTY('ANSI_NULLS') = 1 THEN 'ON;' ELSE 'OFF;' END AS Setting UNION ALL
SELECT ' SET ANSI_PADDING ' + CASE WHEN SESSIONPROPERTY('ANSI_PADDING') = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET ANSI_WARNINGS ' + CASE WHEN SESSIONPROPERTY('ANSI_WARNINGS') = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET ARITHABORT ' + CASE WHEN SESSIONPROPERTY('ARITHABORT') = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET CONCAT_NULL_YIELDS_NULL ' + CASE WHEN SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET NUMERIC_ROUNDABORT ' + CASE WHEN SESSIONPROPERTY('NUMERIC_ROUNDABORT') = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET QUOTED_IDENTIFIER ' + CASE WHEN SESSIONPROPERTY('QUOTED_IDENTIFIER') = 1 THEN 'ON;' ELSE 'OFF;' END
Lowell
April 26, 2017 at 7:41 am
Lowell,
There is only one Exec.
Declare @command varchar (max)
SELECT @command = s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ';' + ' SET QUOTED IDENTIFIER ON; EXEC sp_defaultlanguage @loginame='''
+ name
+ ''',@language=''us_english'''
FROM sys.server_principals
WHERE type_desc = 'WINDOWS_LOGIN'
AND default_language_name <> 'us_english'
AND create_date > '01/May/2016'
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
PRINT @command
if ISNULL(@command,'') <> ''
EXEC(@command)
April 26, 2017 at 8:30 am
not sure what is going on in your case specifically;
i just wrote the attached stuff to explicitly check what my settings are in SSMS vs a TSQL job, vs an execute command in a job, vs changing it inline int he command.
I am seeing that ina job or an EXECUTE, from a job, the setting si ansi nulls off
if i change it inside the EXECUTE command, it is changed ot my expected ansi nulls on.
I created an tracking table in tempdb, and ran the attached job and scripts.
then i simply ran the below,and saw what the values were at the time of execution:SELECT * FROM [tempdb].[dbo].[AnsiSettings] WHERE SETTING IN(' SET QUOTED_IDENTIFIER ON;',' SET QUOTED_IDENTIFIER OFF;')
Setting Source
SET QUOTED_IDENTIFIER ON; SSMS
SET QUOTED_IDENTIFIER OFF;Ansi settings direct TSQL
SET QUOTED_IDENTIFIER OFF;SSMS
SET QUOTED_IDENTIFIER ON; ExplicitQuotedIdentifier Execute @command
Lowell
May 9, 2017 at 8:07 am
Hi Lowell,
Sorry for late reply on this. I ran your scripts and got this output. Probably the same as your output. What else could I do to make this work?
SOURCE SETTING date/time
SSMS SET QUOTED_IDENTIFIER ON; 2017-05-09 14:51:30.770 master
Ansi settings Job TSQL SET QUOTED_IDENTIFIER OFF; 2017-05-09 14:57:21.367 master
Ansi Settings Execute @command SET QUOTED_IDENTIFIER OFF; 2017-05-09 14:57:21.390 master
ExplicitQuotedIdentifier Execute @command SET QUOTED_IDENTIFIER ON; 2017-05-09 14:57:21.413 master
Server version is:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) Mar 26 2015 21:18:04 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Thank you,
Vin
May 9, 2017 at 8:50 am
You won't need a SET QUOTED_IDENTIFIER ON in the dynamic SQL here, just in the main batch.
Your problem is that SET QUOTED_IDENTIFIER takes effect at parse time, not execution time.
Because of that, since you have SET...ON at the beginning and a SET...OFF at the end, the SET...OFF is what is actually in effect.
Just leave the one SET...ON, and it should work.
Cheers!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply