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

Question

I have a SqlDependency setup 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 request causes the OnChanged event to fire continuously with the type Invalid and the Source statement . What, after further research that I found, is what happens when your request breaks the rules, which are the same as the rules for indexed views, because that is what this notification mechanism is based on.

Verification Special Considerations Using Request Notifications (ADO.NET) I do not see any rules that I break with this statement.

Change instruction to

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

. OnChanged .

, ?

Answer 1

ModifyDT?

ADO.Net QN , SQL . :

, / .

datetime, , ( , ADO.Net SQL). : WHERE ModifyDT > @lastDateTime DateTime.

Answer 2

-,

LastExecutionDateTime

SqlDependency SQL SqlCommand

string sql = "SELECT CMRID, SolutionID, CreateDT, ModifyDT " + "FROM dbo.Case " + "WHERE ModifyDT > @lastExecutionDateTime"; 
//notice the parameter @lastExecutionDateTime, you cant use dates as a string, you also cant use something like CONVERT(datetime, '20040508'). You need a real date time object, hence the parameter

//You also only need to use the two part table ref (dbo.x) in the FROM clause, you dont need it on every field
//and while you didnt do it here, if anyone is interested a two part table ref in the form of dbo.[Case] would fail because the brackets will kill your dependency subscription

SqlCommand dependencyCommand= new SqlCommand(sql);
dependencyCommand.Parameters.Add(new SqlParameter("lastExecutionDateTime", SqlDbType.DateTime) {
        Value = LastExecutionDateTime 
    });

//Create a dependency object and associate it with the SqlCommand.
SqlDependency dependency = new SqlDependency();
dependency.AddCommandDependency(dependencyCommand);
//Subscribe to the SqlDependency event.
dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);

, ,

//get the most recent data
DataTable currentDependencyData = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(dependencyCommand);
dataAdapter.Fill(currentDependencyData);

Answer 3

, 3- , , .

"SELECT [CMRID], 
       [SolutionID], 
       [CreateDT], 
       [ModifyDT] 
FROM [dbo].[Case] 
WHERE [ModifyDT] > " + LastExecutionDateTime;