SqlDependency失败，因为无法通知或提供了SELECT语句

Question

我正在尝试使用SqlDependency，我阅读了Microsoft的文章《创建通知查询、查询通知权限》。我反复检查了很多次，似乎都满足了文章中提到的需求。这是我的代码。

private void InitialSqlDependency()
{
using (var connection = new SqlConnection(_connString))
{
connection.Open();
string message = string.Empty;
string query = @"SELECT ModifiedOn FROM [dbo].[ContainerTransactions]";
using (var command = new SqlCommand(query, connection))
{
command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(Dependency_OnChange);
if (connection.State == ConnectionState.Closed)
connection.Open();
SqlDataReader dr = command.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
message = dr[0].ToString();
}
}
}
}
private void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
_logger.Debug("ContainerWatch Dependency Fired!");
if (e.Type == SqlNotificationType.Change)
{
_logger.Debug("ContainerWatch Change Fired!");
this.InitialSqlDependency();
}
}

然而，它总是无法订阅。我看到SqlNotificationInfo返回Query，意思是A SELECT statement that cannot be notified or was provided.这是我的调试img

SELECT语句非常简单，有什么可能导致失败的原因吗？

Answer 1

我找到了根本原因，因为The statement must not reference tables with computed columns。我使用下面的查询来查看计算列

SELECT * FROM sys.computed_columns WHERE object_id = OBJECT_ID('ContainerTransactions')

因此，我认为我不能在此表上使用SqlDependency。