There's a few gotchas in here: you've said that the queries will be short-lived, that you're going to have (relatively) high volume, that you're going to capture parts of the queries, and that you want to communicate back to the app tier. This means you want a non-blocking, high-performance solution, and there's nothing built-in that will make this easy. I'm going to just talk theoretically about something you could build, but it ain't gonna be easy.
Part 1: capturing the query data as it happens. For this, SQL Server's Extended Events are probably the way to go. Think of it as debug points in the SQL Server engine code where you can insert your own code. As you learn about it, you'll find that you can gather all kinds of cool information about queries, but keep in mind that this is blocking code, and the more information you want to gather, the slower your queries go. You won't be able to push data outside of SQL Server in this part - you're going to have to either write the data to an XML file, or put it in the ring buffer. The ring buffer makes the most sense here since you'll need to query it in near-real-time.
Part 2: getting the data from the SQL Server's ring buffer to the client. You could build something to push the data from SQL Server to the overlay tier, but this screams danger to me. It couldn't be single-threaded, because you could hang on trying to send a notification to a client that's running slow, and miss everybody else's notifications. Instead, what I think you want to do is have the overlay app detect on the client side when it needs to check the ring buffer, and then do that via a query. You don't want all the clients continuously polling the ring buffer - performance nightmare there.
The error 9245 message appears to be directly connected to the 'The query notification dialog on conversation handle...' message in that SQL Server 2012 SP1 appears to only show that error a maximum of 10 times (with the same timestamp) in the error log, the rest are aggregated into a single error message:
During the last time interval 257 query notification errors were suppressed.
Which is preceded by the message:
Error: 9245, Severity: 16, State: 1.
Prior to SQL 2012 I do not believe the error notifications were aggregated at all, and did not result in a severity 16 error.
Best Answer
The service specification is the name of the service used for query notifications. The tutorial is focused on SqlDependency, which creates dynamically-named services (GUID appended). No need to explicitly grant
SENDpermissions on the service in that case because the creator of the service will haveCONTROLpermissions on the service anyway.If you were to use the lower-level SqlNotificationRequest object, then you would need to grant
SENDon the service,REFERENCESon the contracts, andRECEIVEon the queues. That allows a less privileged account to subscribe to query notifications.