SET Options From Client

Question

SET Options From Client

curious_sqldba

SSC-Dedicated

Points: 36502
More actions
April 6, 2011 at 3:42 pm

#248496

I have setup a trace to see what are the different set up options used from the client. I am not able to correlate the data what i have. Mentioned below is the O/p. can someone please clarify.
i)Under EventClass i see "Existing Connection" and ApplicationName as ".Net SqlClient Data Provider" with two entries which have different ClientProcessID from same server's . However the Set options are different? Is this showing the SET option from .NET client or something else?
ii) I see SET option as "set transaction isolation level read committed" and if my proc has "set transaction isolation level read uncommitted" which would take ineffect? One from client or one in proc?
Thanks

Viewing 5 posts - 1 through 5 (of 5 total)

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

AlexSQLForums SSChampion Points: 14277 More actions · Answer 1

i)Under EventClass i see "Existing Connection" and ApplicationName as ".Net SqlClient Data Provider" with two entries which have different ClientProcessID from same server's . However the Set options are different? Is this showing the SET option from .NET client or something else?

.NET

ii) I see SET option as "set transaction isolation level read committed" and if my proc has "set transaction isolation level read uncommitted" which would take ineffect? One from client or one in proc?

proc

Alex S

curious_sqldba SSC-Dedicated Points: 36502 More actions · Answer 2

AlexSQLForums (4/7/2011)
i)Under EventClass i see "Existing Connection" and ApplicationName as ".Net SqlClient Data Provider" with two entries which have different ClientProcessID from same server's . However the Set options are different? Is this showing the SET option from .NET client or something else?
.NET
ii) I see SET option as "set transaction isolation level read committed" and if my proc has "set transaction isolation level read uncommitted" which would take ineffect? One from client or one in proc?
proc

Thanks. Interesting to know that SET options inside SQL overwrites SET options from clients..

AlexSQLForums SSChampion Points: 14277 More actions · Answer 3

sqldba_icon (4/7/2011)
AlexSQLForums (4/7/2011)
i)Under EventClass i see "Existing Connection" and ApplicationName as ".Net SqlClient Data Provider" with two entries which have different ClientProcessID from same server's . However the Set options are different? Is this showing the SET option from .NET client or something else?
.NET
ii) I see SET option as "set transaction isolation level read committed" and if my proc has "set transaction isolation level read uncommitted" which would take ineffect? One from client or one in proc?
proc
Thanks. Interesting to know that SET options inside SQL overwrites SET options from clients..

Yeap but not always.

A database option overrides an instance option.

A SET option overrides a database option.

A hint overrides a SET option.

set options set within a dynamic SQL batch affect only the scope of that batch.

set options, such as QUOTED_IDENTIFIER and ANSI_NULLS, are persisted with stored procedure definition and, therefore, take precedence over different values explicitly set for them.

To set instance-wide configuration options, use the sp_configure stored procedure.

To set database-level options, use the ALTER DATABASE statement.

To set the database compatibility level, use the sp_dbcmptlevel stored procedure.

To specify batch-level options (SET options), use the SET statements, such as SET ANSI_PADDING and SET ANSI_NULLS.

Alex S

curious_sqldba SSC-Dedicated Points: 36502 More actions · Answer 4

AlexSQLForums (4/7/2011)
sqldba_icon (4/7/2011)
AlexSQLForums (4/7/2011)
i)Under EventClass i see "Existing Connection" and ApplicationName as ".Net SqlClient Data Provider" with two entries which have different ClientProcessID from same server's . However the Set options are different? Is this showing the SET option from .NET client or something else?
.NET
ii) I see SET option as "set transaction isolation level read committed" and if my proc has "set transaction isolation level read uncommitted" which would take ineffect? One from client or one in proc?
proc
Thanks. Interesting to know that SET options inside SQL overwrites SET options from clients..
Yeap but not always.
A database option overrides an instance option.
A SET option overrides a database option.
A hint overrides a SET option.
set options set within a dynamic SQL batch affect only the scope of that batch.
set options, such as QUOTED_IDENTIFIER and ANSI_NULLS, are persisted with stored procedure definition and, therefore, take precedence over different values explicitly set for them.
To set instance-wide configuration options, use the sp_configure stored procedure.
To set database-level options, use the ALTER DATABASE statement.
To set the database compatibility level, use the sp_dbcmptlevel stored procedure.
To specify batch-level options (SET options), use the SET statements, such as SET ANSI_PADDING and SET ANSI_NULLS.

Thanks

I want to change my proc with the SET options just like what i see from .NET client but i amnot able to do so? Do you know why?

.NET CLient SET options

set quoted_identifier on

go

set arithabort off

go

--set numeric_roundabort

--Go

set ansi_warnings on

go

set ansi_padding on

go

set ansi_nulls on

go

set concat_null_yields_null on

go

set cursor_close_on_commit off

go

set implicit_transactions off

go

set language us_english

go

set dateformat mdy

go

set datefirst 7

go

set transaction isolation level read committed

Go