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

Brian Donahue

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é
>
>
>
>

Brian Donahue

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é
> >
> >
> >
> >
>
>