SQL Server SqlTableDependency无法在生产环境中工作,但可以在测试环境中工作

ee7vknir  于 2022-12-10  发布在  其他
关注(0)|答案(2)|浏览(198)

I have an application that heavily relies on SQlTableDependency, the application run well in our test environment; and we recently restored the database from test env to the production env and enabled service broker on the database the newly restored and we configure our web app to point to the production env , However we realised our web app stoped being notified by the database thru sqltabledependency.
here a sample code of our sqltabledependency handle event

public void SqlTableDependency_Changed(object sender, RecordChangedEventArgs<TableA> e)
{
   if(e.ChangeType == ChangeType.Insert || e.ChangeType == ChangeType.Update)
   {
     var record = e.Entity;
     Logger.GetInstance().Info($"Sqldependency event received with success!!");
     A a = new A()
     {
         ZoneId = record.ZoneId,
         FloorId = record.FloorId
     }
   }

}

In test environment i am able to see logs that sqltabledependency successful received the event, but with production database , there no event notification.
I would appreciate any help.

zysjyyx4

zysjyyx41#

经过几个小时的谷歌搜索,我找到了一个解决方案,只需在恢复的数据库上运行以下命令即可解决问题

ALTER AUTHORIZATION ON DATABASE::[restored db name] TO [sa];

感谢 Remus Rasanu,他回答了关于此stackoverflow Sql Service Broker not working after database restore的类似问题

yhuiod9q

yhuiod9q2#

您的sql连接使用sa帐户?或者甚至您使用另一个sql登录帐户,您需要仍然授予sa的权利?我有类似的问题,sqltvaldependency工作在一些小时,但一些小时,它不再工作

相关问题