Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Posts
    10

    Unanswered: Sql Server 2005 Collation Issue

    (Let me preface this question by stating that I am not a SQL Server admin and usually am not involved in DB maint. And I did not write the app that uses the dbs.)

    I need to move 2 databases from one server to another, both running SQL Server 2005.

    However, the collation on the old server (and the dbs,) is SQL_Latin1_General_CP1_CI_AS. The new server is set up with Latin1_General_CI_AI.

    In the process of it's operation, our app moves data from DB1 into DB2 by using the TempDB.

    After the move, I get this error when trying to start our app - Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.

    Can someone tell me what needs to be done to successfully move the dbs to the new server?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is the compatibility level of both databases set to 90? To find out, run the following:

    Code:
    sp_dbcmptlevel insert your database name here

  3. #3
    Join Date
    Apr 2007
    Posts
    10
    Not sure, I will check.
    Thanks for the feedback.

  4. #4
    Join Date
    Apr 2007
    Posts
    10
    The dbs which I want to move are 80
    The new server master and tempdb are 90

    However, the 2005 instance they are currently on is 90 also
    and they run ok.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Collation in 2005 is a the database level, not the server level.
    Shouldn't be a problem.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Collations in SQL 2000 are also at the database level. The difference is that under SQL 2000 when you have a comparison between a temp table, and a permanent table, the two collations could be out of synch (as you are seeing). In SQL 2005, they changed this behavior so that any temp table is created with the same collation as the database from which the statement was issued.

    If you change to 90 compatibility level, you should not have this problem, as the new behavior should then take hold.

  7. #7
    Join Date
    Apr 2007
    Posts
    10
    FYI - The problem was resolved by detaching/re-attaching the dbs, rather than a backup/restore.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •