Error When Comparing with a View
scarletti
Posts: 3
I'm currently evaluating SQL Data Compare 11.
I'm currently trying to compare 2 replicated tables that have a slightly different schema. The source has a column that is a timestamp, but the target is defined as varbinary(8).
(the columns are the same data types under the cover, so I would have expected the compare to be happy provided the timestamp column isn't the target!!)
Anyhow, to work around this, I've created a view at the source that casts the timestamp column as varbinary(8), which I'm mapping to the target table. This works as long as I don't select the "Use checksum comparison" operator. If I do select that option (which I want to do in order to speed up the comparison), I get a "Validation failure" error telling me that my view doesn't exist.
Running SQL profiler, I can see that it is trying to run sp_table_validation against the view in both the source and the TARGET databases, even though it only exists in the source database.
I'm currently trying to compare 2 replicated tables that have a slightly different schema. The source has a column that is a timestamp, but the target is defined as varbinary(8).
(the columns are the same data types under the cover, so I would have expected the compare to be happy provided the timestamp column isn't the target!!)
Anyhow, to work around this, I've created a view at the source that casts the timestamp column as varbinary(8), which I'm mapping to the target table. This works as long as I don't select the "Use checksum comparison" operator. If I do select that option (which I want to do in order to speed up the comparison), I get a "Validation failure" error telling me that my view doesn't exist.
Running SQL profiler, I can see that it is trying to run sp_table_validation against the view in both the source and the TARGET databases, even though it only exists in the source database.
Comments
This looks like a bug with reference SDC-1874. You might be able to work around it by creating a view in the target, using the same name as the view in the source, that is basically just SELECT * FROM ... and then comparing the views.
Apologies.
Redgate Software
This bug is causing a lot of follow on problems. When I use a view, I lose the parent/child sequencing of the deployment script, i.e. where it would delete child rows before parents, and insert parent rows before child rows. I also have to specify the comparison columns manually with a view.