I am trying to think through a method to use SQL Server 2008 change tracking to verify that data has flowed from a table in a source database to a table in a target database. The target database is fully under my control; I can add things to the source database
but it's not mine. Change tracking would be turned on in both databases. The databases may be on different servers or the same server. The goal is to avoid hard coding the source database name or source server name. And the goal is for the code to be in the
target database. I can't use the Microsoft Sync Framework.
My idea is to isolate recently changed data in both databases and compare the isolated data. I'm encountering obstacles and maybe a showstopper problem in having generalized code in the target database obtain change tracking info from the source database.
For instance to get the source database current version number the CHANGE_TRACKING_CURRENT_VERSION() function has to be executed in the source database. My attempt to specify a linked server name ( EXEC (@cmd) AT linkedservername) as a variable failed to compile.
One change tracking function does indeed allow four-part table names; but another requires an object ID as a parameter.
It seems to me I'm going against the grain of the change tracking feature. I'm posting this message as a reality check to see if that is true or if I am just overlooking so
View Complete Post