Comments
Sort by recent activity
Michelle Taylor wrote:
Are you talking about SQL Compare (which compares schema) or SQL Data Compare (which compares data)?
SQL Data Compare.
For example, our database uses a lot of fields defined as FLOAT(), which only use a total of 32 bits for storage -- 24 bits for the mantissa -- providing only 7 decimal digits of guaranteed precision. Apparently, there are some slight differences in the "rounding" between the two platforms, whether in the FP processor or software, such that the numbers often differ past that. I'd say that SQL Server is converting them on output to a greater precision.
For example, the numbers
109.06
102.81
are displayed by SQL Data Compare as:
109.05999755859375 (32-bit SQL2000)
109.05999755859381 (64-bit SQL2005)
102.80999755859375 (32-bit SQL2000)
102.80999755859381 (64-bit SQL2005)
Knowing that the precision of these numbers is limited to 7 significant digits, they might be rounded and compared as:
102.8100
102.8100 / comments
Michelle Taylor wrote:
Are you talking about SQL Compare (which compares schema) or SQL Data Compare (which compares data)?
SQL Data Compare.
For example, our database uses a lot of fields defi...
Oh, I see, so apparently the checksum is performed at the table level (or selected group of rows defined by the where clause) , not the row level as I had assumed (thus saving a LOT of data transmission across the network, rather than a little). I didn't understand that. I'll definitely leave it off, then.
I also figured out why my latest compares on that table were still timing out and not behaving i/o-wise the way I thought they should (performing a table scan instead of a bookmark lookup): When I was editing my where clause, I was forgetting to select the appropriate table, and apparently the 1st table on the list was selected automatically (though it was scrolled off the screen and invisible any time the list was pulled up) and I was changing the clause on a table I wasn't even checking.... :oops:
So, I think I'm finally figuring this out. I just want to double-check: Any time I add a where clause, I should not expect to see the progress bar move during the procedure, correct?
Thanks for all the info! / comments
Oh, I see, so apparently the checksum is performed at the table level (or selected group of rows defined by the where clause) , not the row level as I had assumed (thus saving a LOT of data transmi...
richardjm wrote:
HKCU\Software\Red Gate\SQL Data Compare 5 (even in v6 - whoops!)
ChecksumTimout - int - number of seconds to timeout.
In my registry, actually, I have a "SQL Data Compare 6" branch, and no "5" (I never installed 5). However, there's no checksum value in there.
Should I create a new ChecksumTimeout value directly under that key (i.e. as a sibling to "ProjectFolder"? Or are you saying that I need to actually create a "...5" key and put it under that? / comments
richardjm wrote:
HKCU\Software\Red Gate\SQL Data Compare 5 (even in v6 - whoops!)
ChecksumTimout - int - number of seconds to timeout.
In my registry, actually, I have a "SQL Data Compare 6" b...
Problem solved. Yes, I was doing something stupid. :oops:
I had been making changes to the WHERE clause and didn't want to lose track of them, so the code looked something like this: fld1 = 'x' and
fld2 between 100 and 300
--fld2 between 50 and 600
Bad decision to use that style of comment -- you never know what's going to be appended to it! Indeed, it was coming out as:
fld1 = 'x' and
fld2 between 100 and 300
--fld2 between 50 and 600 ORDER BY fld1,fld2
Of course, the ORDER BY was commented out!
Adding an extra blank line after the comment would fix the problem, but if you must include a comment, it's safer to use this style:
fld1 = 'x' and
fld2 between 100 and 300
/*fld2 between 50 and 600*/
so there's a definite closure.
Strangely, list compares work much better when the two lists are in the same order.... :roll:
Thanks for the help, guys! / comments
Problem solved. Yes, I was doing something stupid. :oops:
I had been making changes to the WHERE clause and didn't want to lose track of them, so the code looked something like this:fld1 = 'x' an...
I'm sorry, Richard, I totally missed your post right before Kevin's! My fault.
richardjm wrote:
I was confused by your comment of 'Needless to say, this freaked me out big time until I figured out that it wasn't real.' - what do you mean it wasn't real?
Machine A is replicating to machine B. I knew that there were records on A that never made it over to B (92k, as confirmed later by doing the Key-only compare), but it was rather shocking to see it initially report not only 500k of those, but another 600k that supposedly existed on B but weren't on A! (I also spot-checked those records and verified that they DID indeed exist on A, contrary to what it claimed.)
richardjm wrote:
Also can I check you're using SQL Data Compare 6.
Yes, build #1124. BTW, I like the photo that appears after 30 seconds in the about box. It's a bit classier than one of my products where it brings up a cheap pong game with everyone's heads as the balls. 8)
richardjm wrote:
If you can send me your .sdc project files
Okay. I glanced through it and didn't see anything conclusive. I'll pass it on to you.
richardjm wrote:
You can run a profiler on the database in both situations
That's a good suggestion. I also might play with some other things to simplify it and see where I can get the problem to drop out. For example, I'm using a compound key, but my WHERE clause limits the 1st field of the key to a single value, so there's no real need to use that field in the join. Anyway, I'll need to wait until tonight, because the queries murder my data load (in addition to guaranteeing inconsistencies).
Thanks again! / comments
I'm sorry, Richard, I totally missed your post right before Kevin's! My fault.
richardjm wrote:
I was confused by your comment of 'Needless to say, this freaked me out big time until I figured ou...
Any more ideas? This is a pretty important table to us, and it would be nice to know that the product we're considering buying will do what we need it to.
Thanks! / comments
Any more ideas? This is a pretty important table to us, and it would be nice to know that the product we're considering buying will do what we need it to.
Thanks!
Charles wrote:
BTW, It's a bit classier than one of my products where it brings up a cheap pong game
Oh, how fun -- it *is* a game! Turns into a little puzzle after a while. I like it! / comments
Charles wrote:
BTW, It's a bit classier than one of my products where it brings up a cheap pong game
Oh, how fun -- it *is* a game! Turns into a little puzzle after a while. I like it!
kevin wrote:
We discovered a couple of option settings that managed to resolve the issue
[image] You managed to duplicate the problem! [image] You couldn't duplicate it with those options set...
I had started off with none of the comparison options checked. I checked the top two (trim, force), no change. I tried it with the 3rd (show) checked, still no result. I unchecked "show" just so I could force the 4th (use) to be checked, then re-checked it so that all 4 boxes show checked...and still no diffrence. It didn't solve the problem on my system. [image] / comments
kevin wrote:
We discovered a couple of option settings that managed to resolve the issue
You managed to duplicate the problem! You couldn't duplicate it with those options set...
I had start...
I'm experiencing the same thing, though this is the 1st situation where I know for sure it's happening.
I'm comparing two tables with identical schemas, one on the local (2005) machine, and one on a remote (2000) machine.
I'm using a 2-field index as a key, and because the tables involved are large (1.2 billion rows) and the suspect portions are relatively small, I'm using a WHERE clause on the key to limit the area checked. (Yes, this is the same pair of tables I discuss 3 threads after this one)
When I limit the fields to compare to JUST the keys, the comparison results are thus (a only, diff, b only, same):
92599, 0, 0, 566192 (total 658,791)
This is what I expect to see. However, if I allow it to match ALL fields, then these are the numbers I get:
636788, 36, 544189, 21967 (total 1,202,980)
Needless to say, this freaked me out big time until I figured out that it wasn't real.
So, at this point, I can't use the program to sync these two tables; all I can do is use it to generate inserts for the keys, send it to a file, and manipulate it to build a list of keys to feed into a query to bcp out of the one database and into the other.
The behavior is duplicable every time right now; I'd be happy to work with you in the next couple of days to track down what's causing the glitch.
BTW, after reading another post I think I should add --though it shouldn't have anything at all to do with it -- that the 2005 table is partitioned, the 2000 is not. All data being compared is in the same partition. / comments
I'm experiencing the same thing, though this is the 1st situation where I know for sure it's happening.
I'm comparing two tables with identical schemas, one on the local (2005) machine, and one on ...