Tablediff fails when comparing tables that have a DATETIME field as (part of) the primary key, and regional setting is not English

Tablediff fails when comparing tables that have a DATETIME field as (part of) the primary key, and regional setting is not English by Arnoud Berendsen

Closed

as Fixed

Type:	Bug
ID:	749904
Opened:	6/19/2012 6:34:47 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

The TABLEDIFF.exe utility can fail when it finds differences while comparing tables that have a DATETIME column as (part of) the primary key and the Regional Settings on the computer are not set to English (in my case, it is set to Dutch).

This seems to be caused by the fact that a date like 2012-11-17 is rendered as 11/17/2012 in English and as 17-11-2012 in Dutch. When it tries to insert this date into the comparison table that tablediff creates, it apparently incorrectly uses the regional format that gets rendered into the text file, and the insert fails.

When it compares these kind of dates and finds no differences, there is no issue and
Tablediff works as expected.

I've seen this happen on SQL Server 2008 R2 and 2012 both.

Details (expand)

English

SQL Server 2012 - Developer Edition

Tools (SSMS, Agent, Profiler, Migration, etc.)

Windows 7 Enterprise

English

Step 1:

Execute the following script
create table TEMP.Table1 (
    DateField datetime not null,
    primary key(DateField)
    );

create table TEMP.Table2 (
    DateField datetime not null,
    primary key(DateField)
    );

insert into TEMP.Table1 values ('2003-11-17 17:39:03.000')

Step 2: Set Regional Settings on the computer to Dutch.

Step 3:
Execute Tablediff on these tables like so:
"C:\Program Files\Microsoft SQL Server\110\COM\tablediff.exe" -sourceserver MyServer\SQL2012 -sourcedatabase DB -sourceschema TEMP -sourcetable Table1 -destinationserver MyServer\SQL2012 -destinationdatabase DB -destinationschema TEMP -destinationtable Table2 -et TEMP_tablediff -dt -c -o TEMP_tablediff.log

The log file now contains the following error:

Error : The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

The table dbo.TEMP_tablediff, that should be filled with all differences, is empty.

No error, data is correctly compared and reported. Regional settings are ignored.

X64

0 attachments

Posted by Microsoft on 10/30/2012 at 10:31 AM

Hi Arnoud Berendsen,

given my previous message, I'm archiving this work item for now.

Best regards
Jean-Yves Devant
Program Manager Servicing and Lifecycle Experience of High Availability Technologies in SQL Server
Program Manager SQL Server Replication, Change Data Capture, Change Tracking

Posted by Microsoft on 10/8/2012 at 5:25 PM

Hi Arnoud Berendsen ,

thanks for taking the time to share your feedback, this is really important to us.
We are planning to fix this in the next Service Pack of SQL Server 2008 R2 and Service Pack 2 of SQL Server 2012.

Best regards
Jean-Yves Devant
Program Manager Servicing and Lifecycle Experience of High Availability Technologies in SQL Server
Program Manager SQL Server Replication, Change Data Capture, Change Tracking

Posted by Microsoft on 7/10/2012 at 1:52 PM

Hi Arnoud Berendsen,

thanks for taking the time to share your feedback, this is really important to us.
We will investigate the issue and get back to you.

Best regards
Jean-Yves Devant
Program Manager (Replication/CDC/CT)

SQL Server Engine Team