locked
SQL SERVER 2000 and SQL SERVER 2008 RRS feed

  • Question

  • I have a piece of code that works perfectly well in SQL SERVER 2000 but when I try running it in SQL SERVER 2008 I get COLLATION >= errors. Any ideas what I need to do to resolve? Sorry I am at home and don't have the code to hand.
    Wednesday, May 5, 2010 5:06 PM

Answers

  • Collation conflict prevent the engine from joining or comapring two values with each other.I guess simple thing to resolve issue is taking a back up & restore it on sqlserver 2008/2005(or using generate scripts) then run the query.This link can give an little idea abut sql2000 collation conflicts.

    • Proposed as answer by Naomi N Wednesday, May 5, 2010 6:29 PM
    • Marked as answer by KJian_ Tuesday, May 11, 2010 3:24 AM
    Wednesday, May 5, 2010 5:15 PM
  • There is a COLLATE clause that can be used when there are collation problems.  The problem is that your collations do not all match, which is what raises the error.  (Joins across unmatching collations have to choose some method of resolving differences, so the COLLATE clause allows you to define the method.)

    SELECT * FROM A JOIN B ON A.Description COLLATE database_default = B.Description

    SELECT * FROM A JOIN B ON A.Description COLLATE Latin1_General_CI_AS = B.Description COLLATE Latin1_General_CI_AS

    RLF

    • Proposed as answer by Naomi N Wednesday, May 5, 2010 6:29 PM
    • Marked as answer by KJian_ Tuesday, May 11, 2010 3:24 AM
    Wednesday, May 5, 2010 6:23 PM

All replies

  • Collation conflict prevent the engine from joining or comapring two values with each other.I guess simple thing to resolve issue is taking a back up & restore it on sqlserver 2008/2005(or using generate scripts) then run the query.This link can give an little idea abut sql2000 collation conflicts.

    • Proposed as answer by Naomi N Wednesday, May 5, 2010 6:29 PM
    • Marked as answer by KJian_ Tuesday, May 11, 2010 3:24 AM
    Wednesday, May 5, 2010 5:15 PM
  • There is a COLLATE clause that can be used when there are collation problems.  The problem is that your collations do not all match, which is what raises the error.  (Joins across unmatching collations have to choose some method of resolving differences, so the COLLATE clause allows you to define the method.)

    SELECT * FROM A JOIN B ON A.Description COLLATE database_default = B.Description

    SELECT * FROM A JOIN B ON A.Description COLLATE Latin1_General_CI_AS = B.Description COLLATE Latin1_General_CI_AS

    RLF

    • Proposed as answer by Naomi N Wednesday, May 5, 2010 6:29 PM
    • Marked as answer by KJian_ Tuesday, May 11, 2010 3:24 AM
    Wednesday, May 5, 2010 6:23 PM