New User - Require Help with Collation issues

John OliverJohn Oliver Posts: 6
edited February 2, 2010 10:08AM in SQL Data Compare Previous Versions
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.

Comments

  • 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
    Peter Peart
    Red Gate Software Ltd
    +44 (0)870 160 0037 ext. 8569
    1 866 RED GATE ext. 8569
  • Peter,

    Many thanks, I will try this and let you know if this works.
  • 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.
  • 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 [email protected] 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
    Peter Peart
    Red Gate Software Ltd
    +44 (0)870 160 0037 ext. 8569
    1 866 RED GATE ext. 8569
  • 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.
  • 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
    Peter Peart
    Red Gate Software Ltd
    +44 (0)870 160 0037 ext. 8569
    1 866 RED GATE ext. 8569
  • 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.
Sign In or Register to comment.