Collation Conflict Error

  • Hi,

    When I am migrating stored procedure from SQL Server 2000 to SQL Server 2005, I am getting the following error message.

    /**********/

    Msg 468, Level 16, State 9, Procedure sp_ATSR_SwiftAckNaksReport, Line 36

    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in the equal to operation.

    /**********/

    Please find the script of the stored procedure in the attachment.

    Kindly Help me in resolving this issue..

    Regards,

    Ezhilan

  • Is the collation of the 2 servers different?

    It looks like one is case sensitive and one is case insensitive. Is that deliberate, or should both servers be the same?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Yes..you are correct. In one server it is Case sensitive and in other it is not. It is deliberate. How can we resolve this issue?

    Regards,

    Ezhilan

  • Did you copy the database from the other server, or just the proc?

    The server's default colation only affects the system databases and new databases created on that server. Databases copied from elsewhere keep the collation that they were created with

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I am just copying the procedure. Not the the database.

    Regards,

    Ezhilan

  • OK, then it's most likely because the server's collation differs from the databases, or the database has tables in it with different collations.

    Check the collation of the DB that you're running this in, see if its the same as the server's. Check that the collations are what you want.

    You can force a collation when comparing strings of different collations with the COLLATE keyword.

    WHERE Column1 = Column2 COLATE SQL_Latin1_General_CS_AS (or whatever collation you want)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you...Gail Shaw. Its working now.

    Regards,

    Ezhilan

  • Hi,

    we are using COLLATE in expressions only if we can not change the collation as this seems to be very slow... I think you should keep that in mind.

    Regards,

    Jan

  • Hi, Gail.

    Can I have full update SQL?

  • Huh? This thread is 3 years old. What are you asking for?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 9 (of 9 total)

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