SqlTableDependency onchange event not fired

Question

i have an issue with SqlTableDependency. My Changed method is not invoked when i make insert/update/delete to desire table. OnStatusChanged event works OK.

 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 code i based on https://tabledependency.codeplex.com/wikipage?title=SqlTableDependency I am sure that i have and i am using db_owner role. I enabled broker, i see that trigers, services etc is created in mssql db.

Answer 1

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

15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Even though the database already I gave myself full permission, I had yet to do

ALTER AUTHORIZATION ON DATABASE::secret db TO sa

Answer 2

Also check "Note about Compatibility Level and Database Version for tracking record changes" section, https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency.

Even if your SQL Server instance is SQL Server 2008 R2 or latest versions, can be that your Database has been created using an old SQL Server version, for example SQL Server 2005.