SqlTableDependency onchange event not fired

Question

I have a problem with SqlTableDependency. My Changed method is not called when I do insert / update / delete in the wish table. The OnStatusChanged event is working fine.

string conn = @"data source=secret server; integrated security=True; initial catalog=secret db;User id=secret user"; var mapper = new ModelToTableMapper<SqlDataModel>(); mapper.AddMapping(c => c.datavalue, "datavalue"); using (var dep = new SqlTableDependency<SqlDataModel>(conn, "data", mapper)) { dep.OnChanged += Changed; dep.OnStatusChanged += OnStatusChanged; dep.OnError += OnError; dep.TraceLevel = TraceLevel.Verbose; dep.TraceListener = new TextWriterTraceListener(Console.Out); dep.Start(); Console.WriteLine("Press a key to exit"); Console.ReadKey(); dep.Stop(); } } static void OnStatusChanged(object sender, StatusChangedEventArgs e) { Console.WriteLine(e.ToString()); } static void OnError(object sender, ErrorEventArgs e) { Console.WriteLine(e.ToString()); } static void Changed(object sender, RecordChangedEventArgs<SqlDataModel> e) { if (e.ChangeType != ChangeType.None) { var changedEntity = e.Entity; Console.WriteLine("DML operation: " + e.ChangeType); Console.WriteLine("value: " + changedEntity.datavalue); } } 

Above the code, I'm based on https://tabledependency.codeplex.com/wikipage?title=SqlTableDependency I'm sure I have and I use the db_owner role. I turned on the broker, I see that triggers, services, etc. Created in mssql db.

Answer 1

I found an error in my sys.transmission_queue table: An exception occurred while posting a message to the target queue. Error:

15517, State: 1. It is impossible to fulfill the role of the database, since the main "dbo" does not exist, this type of principal cannot be issued or you do not have permission.

Although the database already gave me full permission, I still needed to

 ALTER AUTHORIZATION ON DATABASE::secret db TO sa 

Answer 2

Also check out the “Compatibility Level and Database Version Note for Tracking Changes to Entries” section, https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency .

Even if your instance of SQL Server is SQL Server 2008 R2 or later, the database may have been created using an older version of SQL Server, such as SQL Server 2005.