SqlDependency query with WHERE clause not allowed. How can I modify it to be valid?

Question

I have a SqlDependency set up using the following query:

string sql = "SELECT dbo.Case.CMRID, dbo.Case.SolutionID, dbo.Case.CreateDT, dbo.Case.ModifyDT "
+ "FROM dbo.Case "
+ "WHERE dbo.Case.ModifyDT > '" + LastExecutionDateTime + "'";

Executing this query causes the OnChanged event to fire continuously with a type of Invalid and Source of Statement. Which upon further research I found is what happens when your query breaks rules that are the same as the rules for indexed views since that is what this notification mechanism is based on.

Checking the Special Considerations Using Query Notifications (ADO.NET) I do not see any rules I am breaking with this statement.

Modifying the statement to

string sql = "SELECT dbo.Case.CMRID, dbo.Case.SolutionID, dbo.Case.CreateDT, dbo.Case.ModifyDT "
+ "FROM dbo.Case";

Does work correctly. The OnChanged event only fires when appropriate, and has the correct type set.

So, how can I only return records with a modify date since my last execution of the statement?

Answer 1

What type is ModifyDT?

The ADO.Net reference for QN is incomplete, the complete list is in the SQL reference at Creating a Query for Notification. The latter also lists the following:

The statement must not have comparison or expression based on double/real data types.

Another problem may be the cast from string to datetime that occurs in your query, which may be considered non-deterministic (which is a criteria listed by both ADO.Net and SQL spec). Try using a typed parameter instead: WHERE ModifyDT > @lastDateTime and pass in a parameter of type DateTime.