TMSChangeNotification.OnChange will call more than 1 time after a record amendment.

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
pigbbong
Posts: 9
Joined: Wed 30 Jul 2014 07:17

TMSChangeNotification.OnChange will call more than 1 time after a record amendment.

Post by pigbbong » Wed 30 Jul 2014 08:29

I'm using SDAC change notification to listen to my table data changes, but when I do 1 data amend in my table, TMSChangeNotification will runing more than 1 time :? , I found this because I wan to play sound when data changed, so I put my coding in "TMSChangeNotification.OnChange" but it keep on sound me more than 1 time even I only change 1 data.

I found that many service broker transaction in queue will occur for each update :?:

I'm using Delphi 2007, MSSQL 2008 and SDAC 6.10.19.

Thank you.

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

Re: TMSChangeNotification.OnChange will call more than 1 time after a record amendment.

Post by azyk » Fri 01 Aug 2014 13:38

We couldn't reproduce the problem. Please try reproduce the problem on our SdacDemo project - and let us know the results. The SdacDemo project is in the folder "С:\Program Files\Devart\SDAC for RAD Studio 2007\Demos\Win32\SDACDemo"

If the problem can't be reproduced on our demo, try to compose a small sample to demonstrate the problem and send it to us using the contact form on our website http://www.devart.com/company/contactform.html

pigbbong
Posts: 9
Joined: Wed 30 Jul 2014 07:17

Re: TMSChangeNotification.OnChange will call more than 1 time after a record amendment.

Post by pigbbong » Mon 04 Aug 2014 01:56

Hi,
I found same problem in your try demo also, and I found that the root cause was come from the service broker part, but I'm not sure it's because the standard behavior of service broker or because the dropping step is not complete in change notification component.

The following are my test plan to simulate the problem:
1) Open demo apps and connect change notification. (Service broker named: SDAC_NS_52_QUEUE & SDAC_NS_52 was created.) and test inserting data into dbo.DEPT, everything works fine.
2) Disconnect connection #1, and reconnect again (system get back same SPID, so the service broker name will be same) and test inserting data into dbo.DEPT, now the transaction to service broker queue will be double up become 2 transaction inserted.
3) Repeat test steps #2, (because I'm the only 1 user for this DB so SPID will be remain, and at this moment service broker name will also remain) and the queue transaction will be increase +1 every-time I disconnect and reconnect.

Testing solution: :idea:
I try to change the service broker name generate steps to make sure every time when system generate service broker with unique name, the problem had been resolved. :wink:

May need your clarify are my solution is a correct way to resolve it.

Thank you. :wink:

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

Re: TMSChangeNotification.OnChange will call more than 1 time after a record amendment.

Post by azyk » Tue 05 Aug 2014 15:05

We tried to reproduce the problem according to your test plan. At the 2nd step, the queue and the service in Service Broker were created with the same names, but when inserting data into the the dbo.DEPT table, the TMSChangeNotification.OnChange event always occurred once. Therefore we can't see the reason for the behavior you have described.

Please provide us with the following information:
- The exact version of Microsoft SQL Server and OLE DB provider you use. You can learn it in the Info sheet of TMSConnection Editor
- How do you define the number of this event occurrences in our demo?

pigbbong
Posts: 9
Joined: Wed 30 Jul 2014 07:17

Re: TMSChangeNotification.OnChange will call more than 1 time after a record amendment.

Post by pigbbong » Wed 06 Aug 2014 01:25

Hi,
My MSSQL is version 2008 Express, and I'm using ODBC for the connection. Connection provider I had set to prAuto with PersistSecurityInfo = false. :)

For the number increase definition is below:
When I run the exe for 1st time, service broker queue transaction was correct, 1 transaction inserted to queue per data amended.
Then I close the existing connection and reconnect again, now when I amend 1 data, service broker will insert 2 transactions into service broker queue.
Then I close the existing connection again and reconnect again with the same exe, now when I amend 1 data, service broker will insert 3 transactions into service broker queue.
That is the queue transaction increase +1 after reconnect. :|

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

Re: TMSChangeNotification.OnChange will call more than 1 time after a record amendment.

Post by azyk » Wed 06 Aug 2014 10:45

Hi,
For connection to a database, SDAC uses OLE DB providers. In which way do you use ODBC in SDAC components in your case?

pigbbong
Posts: 9
Joined: Wed 30 Jul 2014 07:17

Re: TMSChangeNotification.OnChange will call more than 1 time after a record amendment.

Post by pigbbong » Fri 08 Aug 2014 00:25

Sorry, I'm wrong, this is OLEDB connection, I connect it via ConnectionString.

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

Re: TMSChangeNotification.OnChange will call more than 1 time after a record amendment.

Post by azyk » Wed 13 Aug 2014 10:13

Please provide us the exact version of OLE DB provider you use. You can learn it in the Info sheet of TMSConnection Editor.

pigbbong
Posts: 9
Joined: Wed 30 Jul 2014 07:17

Re: TMSChangeNotification.OnChange will call more than 1 time after a record amendment.

Post by pigbbong » Thu 14 Aug 2014 01:27

Sorry, when can I get the info sheet for TMSConnection?

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

Re: TMSChangeNotification.OnChange will call more than 1 time after a record amendment.

Post by azyk » Thu 14 Aug 2014 09:31

In design time of your project form, make a double click on the TMSConnection component (that you use for connection to SQL Server). The Edtor window opens. On the Connect tab, set up server connection, and then switch to the Info tab.

pigbbong
Posts: 9
Joined: Wed 30 Jul 2014 07:17

Re: TMSChangeNotification.OnChange will call more than 1 time after a record amendment.

Post by pigbbong » Fri 15 Aug 2014 01:28

Ok get it, the following info show in INFO page:

Microsoft SQL Server: 12.00.2000
Microsoft SQL Server Native Client 10.0: 10.50.1600.1

:P

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

Re: TMSChangeNotification.OnChange will call more than 1 time after a record amendment.

Post by azyk » Mon 18 Aug 2014 14:49

We tried to reproduce the issue in accordance with your recommendations and settings, but, unfortunately, we failed to reproduce it. To investigate the issue further, we need a example, which we could use for the issue reproduction. Therefore, please compose the example, and send it to andreyz*devart*com

Post Reply