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.

Please note that SqlTableDependency is now on https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency — Christian Del Bianco, Aug 09 '17 at 05:15

score 9 · Accepted Answer · answered Dec 27 '16 at 15:03

9

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

answered Dec 27 '16 at 15:03

Kuba Wenta

580
1
5
25

What does this do? – Marcus Aug 01 '18 at 10:28
1

I think this just set database owner to `sa` – Kuba Wenta Aug 02 '18 at 09:09
can anybody explain why it doesn't work without making sa as the owner. I want to keep a different user as the owner. – Prasad Lakmal Apr 26 '19 at 14:30

score 1 · Answer 2 · answered Oct 11 '19 at 06:03

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.

SqlTableDependency onchange event not fired

2 Answers2