Please help with SqlDependency and ASP.NET

Question

I try to obtain in an ASP.NET application using SqlDependency, through a stored procedure, information that contains the last record from a table corroborated with information from other tables. I was stuck in determining the last record ... It seems that the syntax "select TOP 1 * from Table order by id desc" or "select * from Table where id = (select max (id) from Table))" does not work with SqlDependency . What can I do to get the last registration?

Answer 1

I solved the problem: I created a new table on the server with the same structure as the table I want to monitor. To the main table I added a trigger after inserted which empties my secondary table and inserts the new row added. SqlDependency now acts on the secondary table which always has a single record. I couldn't find another more academic method!

Answer 2

Long time since I worked with Query Notification, and I have never used it through SqlDependency. But as I recall, you have a plain query without TOP 1, and you will be waiting until the result set of that query changes. When this happens you can retrieve the rows. I don't think you can call a stored procedure.