Session propery SQL Server 2005


In sql server we can get session property many ways
1. dbcc useroptionsdbcc useroptions
This will give us information about all the enabled setting for that session.
In SQL Server 2005 two new options are available
2. sessionproperty function

It returns set option of a session
You can get information of following option usig set
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_ NULL
NUMERIC_ROUNDABORT
QUOTED_IDENTIFIER

For ex.
select sessionproperty('Quoted_Identifier')
If result is 1 then property on.
3. Dynamic view dm_exec_sessions Benefit of this view that you can see property not only of this session but of also other sessions.You can join this view other dynamic system views using session id.
This view also gives much more information than session property.
If the result is 1 then property is on and if its 0 then property is set off.
select session_id,
login_name,
program_name,
date_format,
quoted_identifier,
arithabort,
ansi_null_dflt_on,
ansi_defaults,
ansi_warnings,
ansi_padding,
ansi_nulls,
concat_null_yields_null,
case transaction_isolation_level
when 0 then 'Unspecified'
when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted'
when 3 then 'Repeatable'
when 4 then 'Serializable'
when 5 then 'Snapshot'
end as transaction_isolation_level,
deadlock_priority
from sys.dm_exec_sessions

No comments:

Post a Comment

Popular Posts