What are the challenges you face when working across database platforms? Take the survey
Options

To collate or not to collate that is the question...

Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
edited February 21, 2003 5:37AM in SQL Compare Previous Versions
Hi Stephane,

Absolutely! You can make SQL Comapre ignore collation order by turning
on the 'Ignore collation order' option in View->Options menu, Objects tab.

Brian Donahue

Technical Support Engineer

Red Gate Software Ltd.

+44 870 1600 037

mailto:brian.donahue@red-gate.com



"Stephane Lagace" <stephane.lagace@buroplus.ca> wrote in message
news:wk3B1s81CHA.1412@server53...
> All my customers run SQL 2000 on Windows 2000 advanced server. However
some
> of them have their server and databases default collation set to
> French_CI_AS and some others use instead the collation
> SQL_Latin1_General_CP1_CI_AS.
>
> Before our last update we never even knew what a collation was so when we
> saw that SQL Compare was adding collation orders to fields creation we
just
> asumed it was OK.
>
> The problem we experienced is that a lot of queries in our application
> started to return error when doing joins or unions on fields of different
> collations.
>
> Is there a way to tell SQL Compare not to add the collation order to field
> creation so we don't need to filter them out before running the script on
> our customers databases.
>
> Thanks
>
> Stéphane Lagacé
>
>
>
>

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Stephane,

    You're correct. If the collation order is specified in the table
    already, SQL Compare is going to migrate that information to the target
    table. So although you've excluded collation order from the comparison, you
    haven't got rid of it.

    The only suggestion I have at the moment is to do a search and
    replace in Query Analyzer when you've loaded the migration script up and
    replace all occurrances of COLLATE SQL_Latin1_General_CP1_CI_AS with
    nothing. Then they should go away permanently, and the table will use the
    default server collation.

    Brian Donahue
    Technical Support Engineer
    Red Gate Software Ltd.
    +44 870 1600 037
    mailto:brian.donahue@red-gate.com

    "Stephane Lagace" <stephane.lagace@buroplus.ca> wrote in message
    news:UVJgBNQ2CHA.1444@server53...
    Sorry to argue but it does'nt do the trick for me.

    With the 'Ignore collation order' option check I can see that SQL Compare
    does not indicate differences if they are only collation differences. But
    for the table that have a real difference (I mean other than just collate)
    when SQL Compare generates the script It will still put collates in the
    create table statement. Like this:

    --
    -- Script for dbo.EQCall
    -- Foreign keys etc. will appear at the end
    --

    PRINT 'Updating dbo.EQCall'
    GO
    @TRANCOUNT&gt;0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN
    TRANSACTION END
    GO
    CREATE TABLE [dbo].[tmp_sc_EQCall]
    (
    [Id] [int] NOT NULL IDENTITY (1, 1) ,
    [IdSucc] [int] NULL ,
    [NoAppel] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [IdClient] [int] NULL ,
    [Idsrvsts] [int] NOT NULL ,
    [IdInAppar] [int] NULL ,
    [IdContrat] [int] NULL ,
    (....)

    Is there à way to get rid of thoses

    Thanks again


    "Brian Donahue (Red Gate)" <brian.donahue@red-gate.com> a écrit dans le
    message de news: ifZN0cE2CHA.1412@server53...
    > Hi Stephane,
    >
    > Absolutely! You can make SQL Comapre ignore collation order by turning
    > on the 'Ignore collation order' option in View->Options menu, Objects tab.
    >
    > Brian Donahue
    >
    > Technical Support Engineer
    >
    > Red Gate Software Ltd.
    >
    > +44 870 1600 037
    >
    > mailto:brian.donahue@red-gate.com
    >
    >
    >
    > "Stephane Lagace" <stephane.lagace@buroplus.ca> wrote in message
    > news:wk3B1s81CHA.1412@server53...
    > > All my customers run SQL 2000 on Windows 2000 advanced server. However
    > some
    > > of them have their server and databases default collation set to
    > > French_CI_AS and some others use instead the collation
    > > SQL_Latin1_General_CP1_CI_AS.
    > >
    > > Before our last update we never even knew what a collation was so when
    we
    > > saw that SQL Compare was adding collation orders to fields creation we
    > just
    > > asumed it was OK.
    > >
    > > The problem we experienced is that a lot of queries in our application
    > > started to return error when doing joins or unions on fields of
    different
    > > collations.
    > >
    > > Is there a way to tell SQL Compare not to add the collation order to
    field
    > > creation so we don't need to filter them out before running the script
    on
    > > our customers databases.
    > >
    > > Thanks
    > >
    > > Stéphane Lagacé
    > >
    > >
    > >
    > >
    >
    >
This discussion has been closed.