Notifications on SQLServer 2012

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
LHSoft
Posts: 104
Joined: Sat 18 Aug 2012 08:33

Notifications on SQLServer 2012

Post by LHSoft » Fri 01 May 2015 22:54

Hello,

I am using SDAC 7.1.3, SQLServer 2012 SP2 x64, Win7 x64, XE5.

Notifications are causing problems again.
With SQLServer 2008 R2 everything is OK, SQLServer 2012 increases the count of refreshes when a table is changed with an TMSSQL:

So, if I start program first time, open the form and cause an update on a table with a TMSChangeNotification, there is 1 refresh after changing. Thats OK and works in good speed. I can repeat this any times and it works fine.

But if closing the form and reopen, then there are suddenly 2 refreshes. Each time, I close the form, reopen and cause a update table, the count of refreshes increases by 1. So there suddenly I have 10, 20 or more refreshes on one update. Program seems like freezing of course.

It does not matter if I close whole program and restart it, next update causes one more refresh.

This is the same with SQLServer 2014 or with SDAC 6.11, but SQLServer 2008 does not show this behavior. I have read that MS has changed the way notifications are working from 2008 to 2012 but I do not see the reason or any solution for my problem yet.

I have examined the table sys.dm_qn_subscriptions:
if executing "KILL QUERY NOTIFICATION SUBSCRIPTION all" it starts with 1 refresh again, but increases further on. But this can not be the way as long as i do not know the Notification-IDs of the queries created in sys.dm_qn_subscriptions from the special TMSNotification used on the form so I can delete only the entries regarding the form. Also setting their timeout to Zero after they have been created does not work.

Do you have any solution?

Best regards
Hans

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Notifications on SQLServer 2012

Post by azyk » Wed 06 May 2015 11:12

Thank you for the information. We have reproduced the problem and will investigate it.

LHSoft
Posts: 104
Joined: Sat 18 Aug 2012 08:33

Re: Notifications on SQLServer 2012

Post by LHSoft » Wed 06 May 2015 13:51

Hello,

thats fine.
Meanwhile I can imagine where the problem might be located: MS talks about "dirty Queues" remaining active in SQL2012/14 other as in SQL2008 where there are remaining too but not active.
So if you would know the regarding QueueID from sys.dm_qn_subscriptions, you could kill those "dirty queues" with example "KILL QUERY NOTIFICATION SUBSCRIPTION QID" on disconnecting Table from Notification, on closing table or something similar. And there would not be remaining anything, wether active nor inactive.

best regards
Hans

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Notifications on SQLServer 2012

Post by azyk » Fri 08 May 2015 12:25

Thank you for the information. We will consider it when investigating this behavior.

Post Reply