Sql-server – Query notifications and connection options

service-brokersql serversql-server-2008transaction

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/

Best Answer

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 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.

Related Question