sql2005迁移到sql2008后 查询通知服务失效

Question

    我用SqlDependency的Onchange事件来捕获数据库某个表的数据实时变化。在Sql2005下，一切正常，但是数据库迁移到sql2008后，无论怎样也捕获不到表数据的变化了。用的sa用户名访问数据库，Broker Services 也启动，程序代码没有任何变化。唯一变化的是服务器路由器增设了防火墙。SQL默认的1433端口已开放，不能确认是否还有其余的端口影响到查询通知服务。此问题已困扰了我3天了，请大家帮忙指点迷津。谢谢！

Answer 1

Trace it in profiler to check what happens in sql.

Answer 2

 I'v tried it. But I didn't find out what's problems. At first, I thought it is because of any ports were disabled. But today we turned off the firewall, it didn't work also.

Answer 3

Did you see its connection on sql server? quick check with sp_who2.

Answer 4

 Hi rmiao, today I'v tried to trace Service Broker in Profiler. After some operations had been performed in my APP. I found there were only two results for Service Broker in Server Profiler.

1.  Broker:Conversation Group ........... 1 - Create........................ sa ...................."

2. Broker:Conversation ............ sa ..................  STARTED_OUTBOUND................initiator..............default..............

It means that my APP just had been created Broker Conversation successful, but Broker Service had not sent message to APP. Right?

What should I do?

Answer 5

Profiler doesn't gather much info of service broker, you can find details in views listed under 'Service Broker Catalog Views (Transact-SQL)' in books online.