How to query for transaction's isolation level...

Hi all,
Is it any way to query a running transaction to see the isolation level it
is using? I'm debugging a mobile .net application that access a SQL 2K
database and I need to verify if it is running transaction on the desired
isolation level (read uncomitted). My idea is to run a test app that left a
transaction opened, and test the isolation level by running some query from
the QueryAnalyzer.
Any hint is welcomed
Thanks in advance
SammyThis is one of the rows from DBCC USEROPTIONS
"SammyBar" <sammybar@.gmail.com> wrote in message
news:%23Bt97iglGHA.4708@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> Is it any way to query a running transaction to see the isolation level it
> is using? I'm debugging a mobile .net application that access a SQL 2K
> database and I need to verify if it is running transaction on the desired
> isolation level (read uncomitted). My idea is to run a test app that left
> a transaction opened, and test the isolation level by running some query
> from the QueryAnalyzer.
> Any hint is welcomed
> Thanks in advance
> Sammy
>
>|||Hi, Sammy
To determine the transaction isolation level currently set for a given
connection, execute the DBCC USEROPTIONS statement from that
connection.
Razvan|||> This is one of the rows from DBCC USEROPTIONS
but can I make a "DBCC USEROPTIONS" not for my own connection, but for
anoter process or spid?|||>> This is one of the rows from DBCC USEROPTIONS
> but can I make a "DBCC USEROPTIONS" not for my own connection, but for
> anoter process or spid?
Not that I know of. If you're interested in it for a specific procedure,
you could probably jam data into a table based on SPID at the beginning of
the proc, then you can query it for any active spids from other sessions.
However, if you're able to modify the proc to do this, you could probably
just check the proc manually to see if the default isolation level is being
overriden.
A|||In SQL Server 2005, the view sys.dm_exec_sessions (one of the replacements
for sysprocesses) shows the isolation level for every connection.
--
HTH
Kalen Delaney, SQL Server MVP
"SammyBar" <sammybar@.gmail.com> wrote in message
news:eGfdsQhlGHA.3528@.TK2MSFTNGP02.phx.gbl...
>> This is one of the rows from DBCC USEROPTIONS
> but can I make a "DBCC USEROPTIONS" not for my own connection, but for
> anoter process or spid?
>