New User - Require Help with Collation issues

John Oliver

I was wondering if any of you guys can help. I have a database with collation set as Latin1_General_CI_AS. I want to extract all the data into a new database with the collation on this database set as SQL_Latin1_General_CP1_CI_AS (This database has already been created as a blank database but with the same tables etc).

When I use the Compare tool, the compare runs, however I am getting over 2000 warnings on completion of the compare and when I try and run the syncronisation I get an error which states

"Error occured executing synchronization: <MsgTag> [Delete]is not applicable.
At least one DSC must exist for Shop Calendar. <MsgTag>
The transaction ended in the trigger. The batch has been aborted."

I would be grateful if someone could help with this, as my SQL skills are minimal at best and I really need to be able to get the data from one database to another.

Many Thanks

John.

peter.peart

Hi John,

Many thanks for your post.

This can be a bit tricky as it is hard to change the collation for an overall database, but hopefully this will work for you:

1) Create new blank database with the new collation
2) Synch existing source database with collation set as Latin1_General_CI_AS to the new target, however set for SQL Compare to ignore collations in the synch
3) Synch the data from source to new database
4) Sych the data from the newly created database to desired source with tables already set up with collation set as SQL_Latin1_General_CP1_CI_AS

Hope that helps,

Pete

John Oliver

Peter,

Many thanks, I will try this and let you know if this works.

John Oliver

Peter

I am getting the following error during the Syncronisation of the database between the two databases -

The following error message was returned from the SQL Server:

[468] Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

The following SQL command caused the error:

/* $Header: /Tools/SQLScripts/Templates/ApplicationDB/S002_IdoColumnWithAliasView.sql 1 3/30/07 3:38p Tblosser $ */
/*
Copyright � 2007 Infor Global Solutions Technology GmbH and/or its affiliates and subsidiaries.
All rights reserved. The word and design marks set forth herein are trademarks and/or registered
trademarks of Infor Global Solutions Technology GmbH and/or its affiliates and subsidiaries.
All rights reserved. All other trademarks listed herein are the property of their respective owners.
*/
CREATE VIEW [dbo].[IdoColumnWithAliasView]
AS
WITH ColumnSchema (TableName, ColumnName, OrdinalPosition, DataType, DataLength, DataDecimalPos, ColumnDataType)
AS
(
SELECT
TABLE_NAME
, COLUMN_NAME
, ORDINAL_POSITION
, dbo.MongooseTypeFromSqlType(DATA_TYPE)
, CASE
WHEN DATA_TYPE IN (N'text', N'char', N'varchar', N'sysname') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE IN (N'ntext', N'nchar', N'nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE IN (N'numeric', N'decimal') THEN NUMERIC_PRECISION
ELSE NULL
END
, CASE
WHEN DATA_TYPE IN (N'numeric', N'decimal') THEN NUMERIC_SCALE
ELSE NULL
END
, ISNULL(DOMAIN_NAME, DATA_TYPE)
FROM INFORMATION_SCHEMA.COLUMNS
)
SELECT
CollectionName
, ColumnSchema.TableName
, ColumnSchema.ColumnName
, ColumnSchema.OrdinalPosition
, TableAlias
, TableAlias + '.' + ColumnSchema.ColumnName AS ColumnWithAlias
, TableType
, JoinType
, JoinText
, DevelopmentFlag
, ColumnSchema.DataType
, ColumnSchema.DataLength
, ColumnSchema.DataDecimalPos
, ColumnSchema.ColumnDataType
FROM ido.Tables t INNER JOIN ColumnSchema ON ColumnSchema.TableName = t.TableName



If there is anything you can do, I would be most grateful, as I am being contsantly asked if this has been resolved yet.

peter.peart

Hi John,

I am going to have to have a go at replicating this myself. It would be great if you could take snapshots of the schema and send them to support@redgate.com quoting the "F00...." reference number you already have, and I can then work with your schema.

If this isn't possible, let me know and I will try and replicate this with my own examples.

Pete

John Oliver

Pete,

I will get a snapshot to you as soon as I work out how to do this.

It should be with you within the hour.

peter.peart

Hi John,

If using SQL Compare 8, you can go to File > Create snapshot and then the tool will create a binary file representation of each set of schema for you.

Pete

John Oliver

Pete,

Thanks, I thought it would be in your software somewhere, was just looking for it.

As I said, my SQL skills are minimal at best.