ApexSQL Knowledgebase: How to synchronize data between databases with different settings

LINKS

General

Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Script

Knowledgebase Home

SITE FEED

Site Feed

Support Forum

The fastest and most effective to get answers to your questions quickly. Click Here for more info.

FAQs

Quick answers to common questions. Click Here for more info.

Troubleshooting

Known issues and basic troubleshooting techniques for problems or unexpected behaviors. Click Here for more info.

Knowledgebase

Technical tips, How-to articles, and other tutorials about ApexSQL Tools. Click Here for more info.

Online Documentation

All of our help files for download or convenient viewing online. Click Here for more info.

Videos

Watch product demos, training videos, or tutorials of our products' main features. We frequently upload new videos so check often for updates. Click Here for more info.

ApexSQL Knowledgebase

Tips and How-to Articles for Apex SQL Tools

How to synchronize data between databases with different settings

DESCRIPTION
How to synchronize data between databases with different settings

The behaviour of DATETIME or SMALLDATETIME type of data can be influenced by client, server, and/or Operating System settings. This KB article describes how to use ApexSQL Diff in situations where these settings must be considered.

SOLUTION
To illustrate, we use three databases located in three different countries. The goal is to synchronize the data between any two of these databases. The main database is hosted on a SQL Server instance with US English settings; the other two databases with France and Germany settings.

Below script creates three databases: General, French, and German. Each database contains one table named t. The table t contains only one DATETIME column named TheDate.

CREATE DATABASE GENERAL
COLLATE LATIN1_GENERAL_CI_AI
GO
CREATE DATABASE FRENCH
COLLATE FRENCH_CS_AI
GO
CREATE DATABASE GERMAN
COLLATE GERMAN_PHONEBOOK_CS_AI
GO
USE GENERAL
CREATE TABLE T
(
THEDATE DATETIME
)
GO

USE FRENCH
CREATE TABLE T
(
THEDATE DATETIME
)
GO

USE GERMAN
CREATE TABLE T
(
THEDATE DATETIME
)
GO

Common problems with temporal data when working in international environments
USA, France, and Germany all use a different date format. To illustrate what happens when data is inserted to above databases, we use the SET LANGUAGE command.

To insert data to General database using the French date format DD/MM/YYYY, we execute below script:

USE GENERAL
SET LANGUAGE ENGLISH
INSERT INTO T VALUES('23/08/2007')

CHANGED LANGUAGE SETTING TO US_ENGLISH.
MSG 242, LEVEL 16, STATE 3, LINE 3

THE CONVERSION OF A CHAR DATA TYPE TO A DATETIME DATA TYPE RESULTED IN AN OUT-OF-RANGE DATETIME VALUE.
THE STATEMENT HAS BEEN TERMINATED.

Due to the difference in language settings, insertion fails. SQL Server cannot resolve the string '23/08/2007' to a valid date format.

To insert the same data to the German database, we execute the following script:

USE GERMAN
SET LANGUAGE GERMAN
INSERT INTO T VALUES('23/08/2007')

DIE SPRACHENEINSTELLUNG WURDE AUF DEUTSCH GEäNDERT.

(1 ROW(S) AFFECTED)

The data is inserted successfully. We do the same thing with the French database using below script:

USE FRENCH
SET LANGUAGE FRENCH
INSERT INTO T VALUES('23/08/2007')

LE PARAMèTRE DE LANGUE EST PASSé à FRANçAIS.

(1 ROW(S) AFFECTED)

Insertion to the French database is successful as well. The results would be the same if we attempted to insert data with the German date format DD.MM.YYYY to our three sample databases; insertion would fail on the General database and it'd be successful on the other two.

On the otherhand, if we attempt to insert data that's in US date format MM/DD/YYYY to all three database, only the insertion to the General database would be successful.
The two other INSERT statements cannot be committed successfully and would raise a conversion error.

Using SQL Server's CONVERT statement to apply the correct date format style before performing the insertion resolves these insertion errors. In the next section, we discuss how to synchronize temporal data in international environments that are similar to our example by using ApexSQL Diff AND without the additional "conversion" step.

Using ApexSQL Diff to synchronize temporal data when working in international environments

Example 1:
In this example, the French database has one row that's not in the General database. To synchronize the General database with the French database, ApexSQL Diff generates below synchronization script:

USE GENERAL
SET LANGUAGE ENGLISH
SET IDENTITY_INSERT [DBO].[T] ON
INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, '20070823 00:00:00.000')
SET IDENTITY_INSERT [DBO].[T] OFF

CHANGED LANGUAGE SETTING TO US_ENGLISH.

(1 ROW(S) AFFECTED)

Example 2:
In this example, the German database has one row that's not in the French database. To synchronize the French database with the German database, ApexSQL Diff generates below synchronization script

USE FRENCH
SET LANGUAGE FRENCH
SET IDENTITY_INSERT [DBO].[T] ON
INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, '20070823 00:00:00.000')
SET IDENTITY_INSERT [DBO].[T] OFF

LE PARAMèTRE DE LANGUE EST PASSé à FRANçAIS.

(1 ROW(S) AFFECTED)

Example 3:
In this example, the General database has one row that's not in the German database. To synchronize the German database with the General database, ApexSQL Diff generates below synchronization script

USE GERMAN
SET LANGUAGE GERMAN
SET IDENTITY_INSERT [DBO].[T] ON
INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, '20070823 00:00:00.000')
SET IDENTITY_INSERT [DBO].[T] OFF

DIE SPRACHENEINSTELLUNG WURDE AUF DEUTSCH GEäNDERT.

(1 ROW(S) AFFECTED)

All of the above scripts complete successfully. By default, ApexSQL Diff uses language-neutral date format settings. This default can be modified by clicking on the Synchronization Wizard button in the Outlook bar then choosing Configuration item from the Graphical Treeview:

The "Apply Date Format" option by default uses the unseparated date format:

The most commonly used formats are also readily available:

To add a format that's not in the list or to use one that already is, check "Apply Date Format", select (or enter) the format, and click the Finish button. The selected format will then be used by ApexSQL Diff on all (SMALL)DATETIME data type columns encountered during the scripting process.

For example, suppose we use the US date format:

The script output would be:

SET IDENTITY_INSERT [DBO].[T] ON
INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, '08/23/2007 00:00:00.000')
SET IDENTITY_INSERT [DBO].[T] OFF

AUTHOR
Frank Kirkland

LAST REVIEW DATE
07 September 2007

Labels: Apex SQL Diff

posted by Janice Lee on Friday, September 07, 2007

0 Comments: