SqlDependency not working with Existing Database

Question

I'm using Signalr with SqlDependency. My code works and it shows me realtime results like I wanted. But the issue is it is working my newly created database. If I change the database to old one the SqlDependency stops work and not getting the change detection on my database table.

Below is my code:

#region SignalRMethods
    [System.Web.Script.Services.ScriptMethod()]
    [System.Web.Services.WebMethod(EnableSession = true)]
    public GlobalApplicationError[] GetErrorsList()
    {
        var cs = "Data Source=.;Initial Catalog=NotifyDB;Integrated Security=True";
        using (var connection = new SqlConnection(cs))
        {
            connection.Open();
            SqlDependency.Start(cs);
            using (SqlCommand command = new SqlCommand(@"SELECT  [Form_Name],[Message],[Prepared_By_Date] FROM [GlobalApplicationError]", connection))
            {
                // Make sure the command object does not already have
                // a notification object associated with it.

                SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                using (var reader = command.ExecuteReader())
                    return reader.Cast<IDataRecord>()
                        .Select(x => new GlobalApplicationError()
                        {
                            Form_Name = x["Form_Name"].ToString(),
                            Message = x["Message"].ToString(),
                            Prepared_By_Date = Convert.ToDateTime(x["Prepared_By_Date"])
                        }).ToList().ToArray();
            }
        }
    }

    private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        MyHub.Show();
    }
    #endregion

Above code perfectly works on database NotifyDB but not on my existing one which is eprocure if I change the database in my connection string. As I'm using the asmx web service so I always update the reference of my web service. Plus I've enable_broker set to true on both databases.

Database screen shots:

NotifyDB

eprocure

output

Kindly let me know what I'm doing wrong in my code. Thanks in advance.

Answer 1

Let windup this. After some brainstorming on internet I successfully found my answer.

I've Checked my database sys.transmission_queue using below query:

select * from sys.transmission_queue

As most likely our notification(s) will be there, retained because they cannot be delivered. The transmission_status have an explanation why is this happening.

I found that there is below error:

Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist

Google it and found the below useful link:

Troubleshooting SQL Server Error 15517

after that I run the below query which is briefly defined in above link

EXEC sp_MSForEachDB 
'SELECT ''?'' AS ''DBName'', sp.name AS ''dbo_login'', o.name AS ''sysdb_login''
FROM ?.sys.database_principals dp
LEFT JOIN master.sys.server_principals sp
ON dp.sid = sp.sid
LEFT JOIN master.sys.databases d 
ON DB_ID(''?'') = d.database_id
LEFT JOIN master.sys.server_principals o 
ON d.owner_sid = o.sid
WHERE dp.name = ''dbo'';';

By doing this, I found several databases that sys.databases said had an owner. However, when I checked it from the database's sys.database_principals, the SID didn't match up for dbo. The column I had for dbo_login came back NULL. That was a clear sign of the issue. There is also the possibility you will see a mismatch between dbo_login and sysdb_login. It appears that as long as dbo_login matches a legitimate login, the error is not generated. I found that on some DBs on one of my servers. While it's not causing a problem now, I'll be looking to correct the mismatch.

Correcting the Error:

The easiest way to correct the error is to use ALTER AUTHORIZATION on the databases which have the NULL login match for dbo. It's as simple as:

ALTER AUTHORIZATION ON DATABASE::eprocure TO sa;

So finally. I got what I want and my SQL Dependency is working fine. This is all from my end. Thanks you help me on this post. I appreciate for your precious time. Happy Coding.