| Author |
Topic |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
 Posted - 2011-04-20 : 14:23:58
|
| One of my SQL scripts is failing, when executed as a part of SQL Agent Job, but succeeds in SSMS. I realized that this is because the default Quoted Identifier setting is OFF for the Agent but, ON for SSMS. Is there a way to set the default setting to ON, for the Agent?------------------------I think, therefore I am - Rene Descartes |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-20 : 14:38:05
|
| Have you tried changing the default connections for the server? Right-click the server node in SSMS, choose Properties, and check quoted identifier under the Connections pane.You should probably restart SQL Agent after you make that change. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2011-04-20 : 15:44:14
|
quote:
Originally posted by tkizer
Why not just add it into the job step?
...to many jobs to modify.------------------------I think, therefore I am - Rene Descartes |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-20 : 16:05:39
|
Run this code and copy the results to a new window:SELECT 'EXEC sp_update_jobstep @job_id=''' + CAST(job_id AS NVARCHAR(36)) + ''',@step_id=1,@command=N''' + N'SET QUOTED_IDENTIFIER ON;' + REPLACE(command,N'''',N'''''') + N'''', *FROM msdb.dbo.sysjobsteps WHERE step_id=1 AND subsystem='TSQL' I'd suggest testing that first on a few jobs before running the whole thing. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2011-04-20 : 16:17:53
|
| Thanks a lot Rob!------------------------I think, therefore I am - Rene Descartes |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-20 : 16:20:55
|
| I should add that you need to run my code in text mode, not grid mode, otherwise you'll lose line breaks that could screw up the update. |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2011-04-20 : 16:25:51
|
quote:
Originally posted by robvolkI should add that you need to run my code in text mode....
...will do that! Thanks a lot. Big Help!------------------------I think, therefore I am - Rene Descartes |
|
|
|
|
SQL Server Agent and Quoted Identifier