A little problem with the Agent

Question

A little problem with the Agent

msimone

SSCommitted

Points: 1842
More actions
January 29, 2018 at 8:02 am

#328273

In a Instance the value for user_options is 5496 from sp_configure.
I have a little work (work's owner is sa) that executes this sentence:
select @@options as option into dbo.test.
when i see the table, the value is 5240.
What is the problem?
Thanks for all.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Session values configured with the SET command override the sp_configure options. Many tools (such as Management Studio automatically configure set options.

John

Sue_H SSC Guru Points: 90860 More actions · Answer 2

msimone - Monday, January 29, 2018 8:02 AM
In a Instance the value for user_options is 5496 from sp_configure.
I have a little work (work's owner is sa) that executes this sentence:
select @@options as option into dbo.test.
when i see the table, the value is 5240.
What is the problem?
Thanks for all.

@@options is used to determine the current set options - it's a bitmap of the current options.
The available options are listed at:
Configure the user options Server Configuration Option

5240 would be the following options:
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
ARITHABORT
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL

Sue

msimone SSCommitted Points: 1842 More actions · Answer 3

John, Sue, thanks for all answers.
I knew that Sets in code are above sp_configure values.
It isn't a problem.
With a trace, I detected a curious thing:
With the event trace ExistingConnection and AplicationName "SQL Agent - Email logger", executes a code with Sets changing then default values.
With ApplicationName "SQL Agent - Job invocation engine", also.
I don't know how to change these Sets for default, when the agent execute the job.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

That doesn't sound all that curious. Why do you want to change it?

John

msimone SSCommitted Points: 1842 More actions · Answer 5

John, why not?
We have definied a default value for user options of 5496 and i don't understand why the job changes it.
I would like to know how to change it.
Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

msimone - Monday, January 29, 2018 9:54 AM
i don't understand why the job changes it.

I'm sure there's a good reason for it. Are you sure it won't break anything if you impose your defaults on it? If it's not causing any problems, my advice is to leave it alone.

John

Sue_H SSC Guru Points: 90860 More actions · Answer 7

msimone - Monday, January 29, 2018 9:54 AM
John, why not?
We have definied a default value for user options of 5496 and i don't understand why the job changes it.
I would like to know how to change it.
Thanks

That's the default setting for quoted identifiers with SQL Agent job steps. It will set that at the beginning of a job step. You can't change the behavior of SQL Agent. If it's affecting a job, you should add SET QUOTED_IDENTIFIER ON to the beginning of the job step.

Sue

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 8

Many of us get lazy with SET options, relying on defaults or assumptions.

There's a reason why SSMS shows these when they script objects. We want to be sure the same object is recreated. If you ever have doubts, expliticly use SET in your scripts.

msimone SSCommitted Points: 1842 More actions · Answer 9

Hello Steve.
I understand and i don't have problems with to put sets in the code. I only said that i would like to know if i can to change default values for the Agent when it start a job.
Thanks for yours answers.