DateTime issue in Data Compare 11

maxpayne1256

Hello,

I'm trying to sync 2 large databases which contains some datetime columns.

Script created by SQL Data Compare version 11.0.0 from Red Gate Software Ltd at 01.11.2014 11:23:53

*/
		
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

-- cut

UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-22 07:39:52.693' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'05fa7102-f781-4000-9ad2-e26a625ffdb7_200 22.09.2014 05:21:10'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-27 07:31:09.077' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'06f4f771-8290-4268-baa1-4642ef47816c_200 25.09.2014 05:16:37'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-27 07:31:09.077' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'06f4f771-8290-4268-baa1-4642ef47816c_200 26.09.2014 05:17:36'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-29 11:05:38.057' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'06f4f771-8290-4268-baa1-4642ef47816c_200 29.09.2014 05:37:56'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-24 07:32:05.493' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'0adcf01f-4d6a-432e-9f14-789acc665517_200 23.09.2014 05:57:25'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-20 07:34:22.210' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'5edf7f16-a83b-4061-8099-e91113702018_200 19.09.2014 05:12:20'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-27 07:31:09.077' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'6146260e-5c34-4483-962d-834250d84c79_200 24.09.2014 14:56:44'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-16 07:35:32.470' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'64560c38-bf80-4559-9fb9-629be193fb62_200 15.09.2014 05:32:03'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-27 07:31:09.077' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'6b4db08c-c971-414d-ab15-e801b40046c0_200 25.09.2014 05:16:57'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-27 07:31:09.077' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'6b4db08c-c971-414d-ab15-e801b40046c0_200 25.09.2014 05:17:05'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-29 11:05:38.057' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'ac025f79-acc6-40df-a08c-331973d4ecf1_200 29.09.2014 05:38:21'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-29 11:05:38.057' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'ac025f79-acc6-40df-a08c-331973d4ecf1_200 29.09.2014 05:38:31'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-20 07:34:22.210' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'ac7847a0-8909-4b08-bcc9-4c033e509a6c_200 18.09.2014 05:23:59'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-16 07:35:32.470' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'c4c9b836-c9c1-4363-a3bd-9192152959bf_200 16.09.2014 05:23:36'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-27 07:31:09.077' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'e07ee28b-3ee1-4759-bf26-3a922d41de10_201 26.09.2014 05:17:58'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-27 07:31:09.077' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'e07ee28b-3ee1-4759-bf26-3a922d41de10_201 26.09.2014 05:18:07'
UPDATE [dbo].[MyTable] SET [LastChanged]='2014-10-17 07:34:52.203' WHERE [MyId]=N'00300' AND [Node]=N'MY-PC' AND [Instanz]=N'e879b3df-fb00-423c-b820-a35c6dd6f412_200 17.09.2014 05:29:12'

There is the following error:

The following error message was returned from the SQL Server:

[242] Bei der Konvertierung eines nvarchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.

My OS Language is de-AT and sql-server os language is de-DE.
I'm using WinXP as Client OS and W2008R2 SP1 as Server OS.
SQL is SQL 2008 R2 (10.50.2550)

I'm really dissatisfied that this problem already occurs in V11 although it seems to be fixed in V8 :-(

Chris.Allen

Can you please post the definitions of the two tables please?

maxpayne1256

Sorry, it was my fault. There was a trigger on that table, which couldn't parse the date correctly because of the changed locale settings in the session. The error in SQL Data Compare was not detailed enough, so I thought that the error already occured during the common insert in the table.