0

I have to monitor one table in a database for changes and replicate them to some table in another database. For some reason, I can not modify the structure of original database, so I can't use triggers, or create constraints, etc. Change Tracking seemed to be ideal solution for what I need, but some of the tables does not have primary keys defined so I'm unable to use it. As for Change Data Capture, here - http://msdn.microsoft.com/en-us/library/bb522511.aspx is written the following:

.

"To enable net tracking, the source table must have a primary key or unique index."

So basically, it seems there is similar problem.

Is there any workaround for such situation? Or maybe I'm missing something?

TX_
  • 5,056
  • 3
  • 28
  • 40

1 Answers1

3

No. Make them have a Primary key. It has one anyway. If that does not work - sorry, WIthout a Primary key, you have Zero idea which tables have really changed. Whoever set that up should be flogged, then have a Little "I Need a Job" advert written for him, for serving burgers at McDonalds - he obviously failed in database design.

Your only choice now is to a complete data comparison on every sync. Have fun. No Workaround. This Technology Needs a Primary key defined, and if you can not use Triggers or anything else, then a "select" and a code side data comparison (which will be slow) is your only choice.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • 1
    I could add, that without PK change tracking makes no sense. There have to be something that **uniquely** identifies a row **for it's whole lifetime**. Otherwise either if there are duplicate rows, or rows whose all columns can be changed at once, tracking behaviour is unclear. – Mihail Golubev Jan 05 '13 at 14:27
  • 1
    Yes and no. I assume the rows do have a logical PK, just it was not set into the database as such. Could even be a unique index, just not a PK, for the server. – TomTom Jan 05 '13 at 16:42