Post reply

what are a given user's connection properties?

  • June 4, 2013 at 1:47 pm

    #273501

    I'm having trouble with my keyword searching today, hopefully someone can help. I'm trying to find out what a given SPID's connection properties are? Something like what DBCC UserOptions return but for a given SPID. Specifically, what is the isolation level of a given SPID.

    Thanks in advance!!!!

  • June 4, 2013 at 2:02 pm

    #1621097

    i knew there was a dmv with this info (select * from sys.dm_exec_sessions) , but i had to google it myself to be sure;

    this blog has a nice explanation and example:

    http://sqlblogcasts.com/blogs/martinbell/archive/2009/06/15/Other-ways-to-see-User-Options.aspx

    here's the link to the text of the end result script:

    http://sqlblogcasts.com/blogs/martinbell/Misc_Scripts/Unpivot_dm_exec_session.sql.txt

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 4, 2013 at 3:23 pm

    #1621126

    select session_id, transaction_isolation_level

    from sys.dm_exec_sessions

    where session_id = <your_SPID>

    it will return numeric values.

    0 = Unspecified

    1 = ReadUncomitted

    2 = ReadCommitted

    3 = Repeatable

    4 = Serializable

    5 = Snapshot

Viewing 3 posts - 1 through 2 (of 2 total)

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