Periodically SqlNotificationRequest fails to register a SQL query notification subscription (sys.dm_qn_subscriptions is an empty set). There doesn't appear to be a way to look up the UserData in the subscriptions table to verify that the request registered
a notification subscription. How is this done?
var request = new SqlNotificationRequest
{
UserData = registrationKey.ToString(),
Options = options,
Timeout = MAX_REQUEST_TIMEOUT_SECONDS
};
using (var dependencyConnection = new SqlConnection(connectionString))
{
dependencyConnection.Open();
using (var dependencyCmd = new SqlCommand(myquery, dependencyConnection))
{
dependencyCmd.CommandTimeout = MAX_REQUEST_TIMEOUT_SECONDS;
dependencyCmd.Notification = request;
using (var reader = dependencyCmd.ExecuteReader())
{ // data is returned but there is no subscription