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.

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.

• Edited by Johns-305 [boatseller] Friday, January 30, 2015 1:42 PM

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.

• Edited by Johns-305 [boatseller] Friday, January 30, 2015 1:42 PM

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.

• Edited by Johns-305 [boatseller] Friday, January 30, 2015 1:42 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.

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 xuMicrosoft contingent staff, Moderator 21 hours 33 minutes ago

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 xuMicrosoft contingent staff, Moderator Thursday, February 05, 2015 1:53 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 xuMicrosoft contingent staff, Moderator Thursday, February 05, 2015 1:53 PM