Post reply

Collation Problem in migration

  • March 22, 2005 at 10:07 am

    #87826

    Hi,

    I just migrated a SQL65 server with a collation(Sort order 30 on bin-CP437)  to a SQL2K server with default collation (Sort oder 52 on CP 1252). Now I got a problem in a View with Select Distinct statement. in SQL2K. It bascially gives different result as shown in SQL65. Could you some give me some ideas as to how to fix it? 

     

    Thanks

     

    John

     

     

  • March 22, 2005 at 10:49 am

    #548250

    This is the way to change collation in a query.

    Note that you can also change it in the table definition, but that would obviously change every query that touches the field(s) with modified collation.

    Select count(distinct name collate Latin1_General_CS_AS) from dbo.ObjSQL --case and accent sensitive

    --count = 3435

    Select count(distinct name collate Latin1_General_CI_AI) from dbo.ObjSQL --case and accent IN - sensitive

    --count = 3336

    Select count(distinct name collate Latin1_General_CS_AI) from dbo.ObjSQL --case sensitive and accent IN - sensitive

    --count = 3425

    Select count(distinct name collate Latin1_General_CI_AS) from dbo.ObjSQL --case IN - sensitive and accent sensitive

    --count = 3348

  • March 22, 2005 at 12:29 pm

    #548298

    I got it. Thanks a lot!!

  • March 22, 2005 at 12:32 pm

    #548302

    HTH. Did you change the collation only in the query or in the table DDL?

  • March 22, 2005 at 12:39 pm

    #548304

    In query.

    Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply