Different Rows out by 1??

fatherjack2

hi,

I have a situation where I have two databases that I have to merge into 1 but there are common ID's with different row data so am having to pick my way through the transfer carefully. Having run SQL Compare on DB1 vs DB2 it tells me that DB1 only has 121 rows, there are 0 different rows and DB2 only has 147 rows and there are zero identical rows.

I have run a SQL script

SELECT * FROM [DB1].[dbo].[UserEvents] AS ae2
go
SELECT * FROM [DB2].[dbo].[UserEvents] AS ae3
go

this returns

(122 row(s) affected)

(147 row(s) affected)

Why would that be?

Answers on a 1st class postcard addressed to :
the guy with the urgent data merge project this weekend
Cornwall

thanks !!


Jonathan

Chris Auckland

We worked on this issue through a private support ticket, here was the conclusion.

It seems that SQL Data Compare is designed to read uncommitted transactions to increase concurrency. When we issue the select statement, we do it with NOLOCK. This is the information from BOL: "Specifies that dirty reads are allowed. This means that no shared locks are issued and no exclusive locks are honored. Allowing dirty reads can result in higher concurrency, but at the cost of lower consistency. If READUNCOMMITTED is specified, it is possible to read an uncommitted transaction or to read a set of pages rolled back in the middle of the read; therefore, error messages may result. For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL. "

I think this could be why there is a discrepancy between your SQL Query, and what SQL Data Compare reports.

I have logged a feature request to add an extra option to force SQL Data Compare to only consider committed transactions. The feature tracking code for this is SDC-1026.