Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm investigating a problem with query notifications. The platform is SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Standard Edition.

.NET errors show a problem reported from SqlNotificationInfo Enumeration (http://msdn.microsoft.com/en-gb/library/system.data.sqlclient.sqlnotificationinfo(v=vs.80).aspx ) as: Event Reason: Isolation.

During tracing I notice that the audit login event immediately preceding the Broker conversation shows:

 -- network protocol: TCP/IP
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read uncommitted

So, my working theory is that the transaction isolation level used for the conversation is not supported. But where is this set and how can I change it?

I've read about a similar problem here:

http://www.roelvanlisdonk.nl/?p=3144 and

http://www.sqlskills.com/blogs/bobb/watch-the-sqlnotificationinfo-for-query-notifications/

share|improve this question
    
Don't understand why most connection are being made as read committed but these particular connections are read uncomitted –  reticentKoala Feb 19 '13 at 16:55
    
You or a developer will need to debug the application to figure out where the isolation level is being set on the connection being used to run the query. –  Jon Seigel Feb 19 '13 at 17:57
    
It's the same application, same connection string, hence the confusion. –  reticentKoala Feb 20 '13 at 14:54
add comment

1 Answer 1

up vote 0 down vote accepted

The explanation to this problem turned out to be an issue with another part of the application explicitly opening read uncommitted connections. These read uncommitted connections were then left in the connection pool and reused by service broker, which can't can't operate using this isolation level.

The solution was simple: Make sure service broker is connecting with a different connection string, thereby using a different connection pool.

share|improve this answer
    
You can even find SET options for a current session as described here - mssqltips.com/sqlservertip/1415/… –  Kin Mar 28 '13 at 16:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.