SqlDependency not working with Existing Database

user_5869145 0
Viewed 2289

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

enter image description here

eprocure

enter image description here

enter image description here

output

enter image description here

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

sqldependency signalr webforms c#
2 Answers

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.

user_58691450

Please make sure Query Notifications & Service broker are enabled and permissions for the IIS identify are granted.

Steps to enable : https://techbrij.com/database-change-notifications-asp-net-signalr-sqldependency

To check service broker is enabled execute the below statement

SELECT name, is_broker_enabled FROM sys.databases

To enable service broker

ALTER DATABASE <<DatabaseName>> SET ENABLE_BROKER

To grant permission to a user

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO “<<USERIDENTITY>”
Ramesh_305940
Related