SQL Data Compare finds differences that don't appear to be there

Brian Donahue

Hi Josh,

Thanks for the very detailed problem description! I've reproduced the
problem here in-house and I can tell you that it's not supposed to behave in
this way. We should be able to get this fixed, if at all possible, in time
for the next release of the SQL Bundle.

Thanks again for pointing out the problem.

Kind Regards,

Brian Donahue
Red Gate Technical Support


"Josh Venman" <joshv_nospam@ideasinternational.com> wrote in message
news:mu3xy1ZgDHA.1228@server53...
> Hi,
>
> I purchased SQL Data Compare to assist in investigating a problem with the
> two ends of our merge replication setup being out of synch. It's a huge
time
> saver but an issue has come up with the tool that stops me moving forward
> with it as a reliable test of "sameness". Bear with me in this because
it's
> a fairly length explanation.
>
> We have a setup with SQL Server A running here in Sydney and SQL Server B
> running in the UK. Data entry occurs in both locations and merge
replication
> keeps both databases in synch. Some data that didn't get replicated
between
> the two sites is the reason for this investigation.
>
> One of the first things I did was use SQL Data Compare to analyse the
> differences (if any) between the tables in the databases on both servers.
> This highlighted the problem areas and was a good basis for moving forward
> with a more detailed look at what the differences are and why they are
> there. The problem I have come across however is that for at least two of
> the tables, SQL Data Compare is reporting Missing and Additional records
> incorrectly.
>
> So getting to the specifics, one of the tables, "cpFeature" that is coming
> up with differences has this schema:-
>
> featureid uniqueidentifier 16 (default value newid())
> name varchar 32
> class varchar 64
> description varchar 256 (nulls allowed)
> status char 10 (nulls allowed) (default value 'Current')
>
> The primary key is "featureid".
>
> When I compare the copy of this table in Sydney with the one in the UK,
SQL
> Data Compare tells me that there are 5 different records, 1 missing record
> and 8 additional records. See the attached screen shots, Missing.gif and
> Additional.gif and note that the row highlighted as missing is also
present
> in the in the additional records in exactly the same form... By
definition,
> it shouldn't be in both places to have been identified as either missing
or
> additional.
>
> So from here I started to do some additional testing taking the data out
of
> the complications of replication..
>
> (1) I created a local (Sydney) copy of the Sydney side version of the
table
> using DTS in a new database and ran SQL Data Compare against that.
Outcome:
> no differences.
>
> (2) I created a new database on the UK server and used DTS to copy the
> Sydney-side data up to it, then ran SQL Data Compare against the two.
> Outcome: the problem as described - the row with featureid =
> '1f07bf2f-69a6-4222-a098-475948f80fd6' is flagged as missing and
additional
> when it's actually in both places.
>
> I can verify that it really is in both tables by doing a join between the
> two - SQL Query Analyser happily finds the offending row in both tables.
> Equally, if I get Query Analyser to give me all rows that are in the
Sydney
> table that are NOT in the UK table, I get no returns.
>
> (3) I created a new database on the Sydney server and used DTS to copy the
> UK-side data down to it, then ran SQL Data Compare against the two.
Outcome:
> the problem as described. Doing the same query analyser tests show that
the
> offending row is in both tables.
>
> (4) I created a second copy of the UK-side data on the UK server in
another
> new database and ran SQL Data Compare against the two. Outcome: no
> differences.
>
> (5) With the thought that it might be something to do with comparing cols
of
> type uniqueidentifier, I tried changing col type of "featureid" to
> varchar(50) on both sides, then ran SQL Data Compare again. Outcome: no
> differences!
>
> (6) I then changed featureid back to type uniqueidentifier and ran SQL
Data
> Compare again. Outcome: problem as described...
>
> So from this, it looks as though there is something about these two sets
of
> data that is causing the comparison used in SQL Data Compare to give a
false
> result and perhaps it's related to uniqueidentifier type cols maybe in
> connection with the primary key.
>
> The problem doesn't go away when DTS is used to copy the data elsewhere so
> it doesn't appear to be some kind of issue with the structure of a
> particular database or table in that database.
>
> Maybe this will ring a bell for some existing issue you know about it.
> Either way I can provide you with the extracted data if you wish to do
your
> own testing. For now I'm stuck in what I was trying to do because SQL Data
> Compare is apparently not giving me an accurate picture of the data diffs
> between these two tables.
>
> Regards,
> Josh
>
>
> --
>
>
> Josh Venman
>
>
>

Brian Donahue

Hi Josh,

We're in the final stages of a new release, so at the latest, 14
October. If it's feasable, we will send you a patch directly as soon as it
gets fixed.

Regards,

Brian Donahue
Red Gate Technical Support

"Josh Venman" <joshv_nospam@ideasinternational.com> wrote in message
news:sTRIEAhgDHA.2912@server53...
> Thanks Brian,
>
> My need for the tool is pretty immediate for it to be of value in this
> exercise. Can you give me some idea of when that new release is planned
for
> or alternatively maybe offer a patch for the current version? If it isn't
> going to be days away then I will have to author something to do the table
> comparison myself. This does seem like a pretty fundamental problem given
> the purpose of the tool so I'm hoping you can pull something out of the
hat
> for me! :-)
>
> Rgds,
> Josh
>
> "Brian Donahue (Red Gate)" <brian.donahue@red-gate.com> wrote in message
> news:pFnisqegDHA.2912@server53...
> > Hi Josh,
> >
> > Thanks for the very detailed problem description! I've reproduced
the
> > problem here in-house and I can tell you that it's not supposed to
behave
> in
> > this way. We should be able to get this fixed, if at all possible, in
time
> > for the next release of the SQL Bundle.
> >
> > Thanks again for pointing out the problem.
> >
> > Kind Regards,
> >
> > Brian Donahue
> > Red Gate Technical Support
> >
> >
> > "Josh Venman" <joshv_nospam@ideasinternational.com> wrote in message
> > news:mu3xy1ZgDHA.1228@server53...
> > > Hi,
> > >
> > > I purchased SQL Data Compare to assist in investigating a problem with
> the
> > > two ends of our merge replication setup being out of synch. It's a
huge
> > time
> > > saver but an issue has come up with the tool that stops me moving
> forward
> > > with it as a reliable test of "sameness". Bear with me in this because
> > it's
> > > a fairly length explanation.
> > >
> > > We have a setup with SQL Server A running here in Sydney and SQL
Server
> B
> > > running in the UK. Data entry occurs in both locations and merge
> > replication
> > > keeps both databases in synch. Some data that didn't get replicated
> > between
> > > the two sites is the reason for this investigation.
> > >
> > > One of the first things I did was use SQL Data Compare to analyse the
> > > differences (if any) between the tables in the databases on both
> servers.
> > > This highlighted the problem areas and was a good basis for moving
> forward
> > > with a more detailed look at what the differences are and why they are
> > > there. The problem I have come across however is that for at least two
> of
> > > the tables, SQL Data Compare is reporting Missing and Additional
records
> > > incorrectly.
> > >
> > > So getting to the specifics, one of the tables, "cpFeature" that is
> coming
> > > up with differences has this schema:-
> > >
> > > featureid uniqueidentifier 16 (default value newid())
> > > name varchar 32
> > > class varchar 64
> > > description varchar 256 (nulls allowed)
> > > status char 10 (nulls allowed) (default value 'Current')
> > >
> > > The primary key is "featureid".
> > >
> > > When I compare the copy of this table in Sydney with the one in the
UK,
> > SQL
> > > Data Compare tells me that there are 5 different records, 1 missing
> record
> > > and 8 additional records. See the attached screen shots, Missing.gif
and
> > > Additional.gif and note that the row highlighted as missing is also
> > present
> > > in the in the additional records in exactly the same form... By
> > definition,
> > > it shouldn't be in both places to have been identified as either
missing
> > or
> > > additional.
> > >
> > > So from here I started to do some additional testing taking the data
out
> > of
> > > the complications of replication..
> > >
> > > (1) I created a local (Sydney) copy of the Sydney side version of the
> > table
> > > using DTS in a new database and ran SQL Data Compare against that.
> > Outcome:
> > > no differences.
> > >
> > > (2) I created a new database on the UK server and used DTS to copy the
> > > Sydney-side data up to it, then ran SQL Data Compare against the two.
> > > Outcome: the problem as described - the row with featureid =
> > > '1f07bf2f-69a6-4222-a098-475948f80fd6' is flagged as missing and
> > additional
> > > when it's actually in both places.
> > >
> > > I can verify that it really is in both tables by doing a join between
> the
> > > two - SQL Query Analyser happily finds the offending row in both
tables.
> > > Equally, if I get Query Analyser to give me all rows that are in the
> > Sydney
> > > table that are NOT in the UK table, I get no returns.
> > >
> > > (3) I created a new database on the Sydney server and used DTS to copy
> the
> > > UK-side data down to it, then ran SQL Data Compare against the two.
> > Outcome:
> > > the problem as described. Doing the same query analyser tests show
that
> > the
> > > offending row is in both tables.
> > >
> > > (4) I created a second copy of the UK-side data on the UK server in
> > another
> > > new database and ran SQL Data Compare against the two. Outcome: no
> > > differences.
> > >
> > > (5) With the thought that it might be something to do with comparing
> cols
> > of
> > > type uniqueidentifier, I tried changing col type of "featureid" to
> > > varchar(50) on both sides, then ran SQL Data Compare again. Outcome:
no
> > > differences!
> > >
> > > (6) I then changed featureid back to type uniqueidentifier and ran SQL
> > Data
> > > Compare again. Outcome: problem as described...
> > >
> > > So from this, it looks as though there is something about these two
sets
> > of
> > > data that is causing the comparison used in SQL Data Compare to give a
> > false
> > > result and perhaps it's related to uniqueidentifier type cols maybe in
> > > connection with the primary key.
> > >
> > > The problem doesn't go away when DTS is used to copy the data
elsewhere
> so
> > > it doesn't appear to be some kind of issue with the structure of a
> > > particular database or table in that database.
> > >
> > > Maybe this will ring a bell for some existing issue you know about it.
> > > Either way I can provide you with the extracted data if you wish to do
> > your
> > > own testing. For now I'm stuck in what I was trying to do because SQL
> Data
> > > Compare is apparently not giving me an accurate picture of the data
> diffs
> > > between these two tables.
> > >
> > > Regards,
> > > Josh
> > >
> > >
> > > --
> > >
> > >
> > > Josh Venman
> > >
> > >
> > >
> >
> >
>
>