Post reply

SQL Server collation vs Database Collation

  • June 11, 2020 at 9:23 am

    #3762217

    Hi Team,

    My server collation is :Latin1_General_CI_AS

    Data base collation is :SQL_Latin1_General_CP1_CI_AS

    server and database collations are different.

    now while connecting application to db we are getting below error. is server and db collations should be same to connect application please advice

    System.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

    at System.Data.SqlClient.T

     

  • June 11, 2020 at 9:54 am

    #3762226

    Three options

    1. Rebuild the system databases using the database collation.  Note you will need to backup all databases, logins, server side objects etc and replay them

    2.  Create new DB with server collation, copy all objects to new DB using the new collation, copy all the data from Old to New

    3.  Change the application to use the COLLATE DATABASE_DEFAULT option to force one of the collations.

  • June 11, 2020 at 10:15 am

    #3762236

    Thanks for your inputs...

    we will fallow any one of the option.

     is server and db collations should be same right always?  

     

     

  • June 11, 2020 at 10:21 am

    #3762238

    Not necessarily, it is the old "it depends", for example ReportServer database for SSRS has to be in a specific collation for it to work, which is always different from the server collation I implement for the UK code page.

  • June 11, 2020 at 10:37 am

    #3762243

    Thanks.. yes, report server is okay.. but here user database and my application is not able to authenticate with db.. login page also failing.let me check once after enabling trace and see if anyone using collation from application side

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

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