none
WCF-SQL notification and SQL Change Data Tracking - incompatible? RRS feed

  • Question

  • Hi,

    I have a DB with Change Data Tracking enabled on a particular table. Each time an Update/Insert is made on this table, I'm able to check what need to be synchronised with other systems by using the CHANGETABLE function of SQL Server.

    And I want to use it with a Receive location WCF-SQL with inboundOperationType=Notification. So each time a modification is made on the table, I have a notification and I'm able to retrieve the updated/inserted records.

    Unfortunately, it seems that using the CHANGETABLE is not supported in the NotificationStatement, I got the following error: "The notification callback returned an error. Info=Invalid. Source=Statement. Type=Subscribe.".

    Any ideas to workaround?

    Thanks.

    Friday, January 30, 2015 1:16 PM

Answers

  • Hi Johns-305,

    Thank you for your response.

    I'm not totally agreed with you about the notification because it depends on the situation.

    In my case, I have a table in a remote SQL database where data are not changing often. But I want to retrieve the changes as fast as I can because it needs to be synchronised with a MDM. 

    The notification is great for me because I get a notification message just after a change and I can synchronise it. Of course, if my receive location is down I can't receive the notification but with the mechanism described in the MSDN article you pointed, I can get the changes I missed during the outage.

    And now I have a solution to use the notification and the Data Change tracking: I use a regular SELECT statement in the notification statement (SELECT <columns> FROM MYTABLE). If something changes in the table, I got a notification and then I send a SELECT FROM CHANGETABLE(MYTABLE, ...) and I store the number of the last modification I got. So I can retrieve only the changes that occurs since my previous call.


    • Marked as answer by Angie Xu Thursday, February 5, 2015 1:53 PM
    Tuesday, February 3, 2015 5:47 AM

All replies

  • While CHANETABLE itself is not supported, you should be able to get the same behavior using a simple SELECT statement in the NotificationStatement.

    However, I will offer that the Notifications feature of the SQL Adapter is a bit fragile and since its easy enough to miss notifications, I've not used it.

    Discussed here: https://msdn.microsoft.com/en-us/library/dd788506.aspx

    Polling is much more reliable.

    Friday, January 30, 2015 1:29 PM
  • Hi Johns-305,

    Thank you for your response.

    I'm not totally agreed with you about the notification because it depends on the situation.

    In my case, I have a table in a remote SQL database where data are not changing often. But I want to retrieve the changes as fast as I can because it needs to be synchronised with a MDM. 

    The notification is great for me because I get a notification message just after a change and I can synchronise it. Of course, if my receive location is down I can't receive the notification but with the mechanism described in the MSDN article you pointed, I can get the changes I missed during the outage.

    And now I have a solution to use the notification and the Data Change tracking: I use a regular SELECT statement in the notification statement (SELECT <columns> FROM MYTABLE). If something changes in the table, I got a notification and then I send a SELECT FROM CHANGETABLE(MYTABLE, ...) and I store the number of the last modification I got. So I can retrieve only the changes that occurs since my previous call.


    • Marked as answer by Angie Xu Thursday, February 5, 2015 1:53 PM
    Tuesday, February 3, 2015 5:47 AM