none
Receiving collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" error.

    Question

  • Hi all,

    When I am trying to check the properties of any of my system\user defined databases i am receiving below error:

    "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. (Microsoft SQL Server, Error: 468)"

    Please let me know how i can fix it.

    All DB compatibility level is 100. The Collation of master/msdb is 'Latin1_General_CI_AS' and is different from collation of model/tempdb 'SQL_Latin1_General_CP1_CI_AS'.

    I am doubting this is the issue and as we can't change the collation of system databases... do i have the only option of rebuilding system database or rebuilding instance left with me?????? or any other options to fix this???

    Thanks In Advance


    VVinayPrasad

    Tuesday, March 06, 2012 10:26 AM

Answers

All replies

  • Hi

    use COLLATE ..

    http://msdn.microsoft.com/en-us/library/ms184391.aspx 

    http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/ 

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Tuesday, March 06, 2012 10:30 AM
  • Hi Winay,

    Go throw below link i hope that will help you :

    http://www.sqlservercentral.com/articles/T-SQL/61288/

    Tuesday, March 06, 2012 10:34 AM
  • All DB compatibility level is 100. The Collation of master/msdb is 'Latin1_General_CI_AS' and is different from collation of model/tempdb 'SQL_Latin1_General_CP1_CI_AS'.

    That is error you get if you mix collations in a query.

    The patch is the COLLATE clause:

    http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/

    The substantial solution is making all db-s on the server the same collation, if that is feasible.

    In the above case temporary tables are created with the default tempdb collation unless the collation explicitly declared on the table columns.

    To change column collation:

    http://www.sqlusa.com/bestpractices/changecollation/

    Collation is actually a column property. Database and server collation are just default values. When you change a database collation, only the default value changes. Table columns remain the same collation as they were before.

    Related articles:

    Consider Your Collations Carefully, Or Pay Later

    How to change the collation of TEMPDB

    Changing the collation of the tempdb database


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES




    • Edited by Kalman Toth Tuesday, March 06, 2012 6:09 PM
    • Marked as answer by VVinayPrasad Thursday, March 15, 2012 6:45 AM
    Tuesday, March 06, 2012 1:18 PM
  • So that is not supposed to happen. The system databases are supposed to all have the same collation. Do you know if there have been any special actions taken with this instance?

    I have seen people who have had this situation before, but I don't recall what the resolution is. But one possibiliy is to take the model database from another instance with the collation Latin1_General_CI_AS and copy these files over the files for this instance.

    The blog post that Kalman potined to suggested something like this, although it seemed to be aimed to create exactly the mess you are seeing.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, March 06, 2012 2:07 PM
  • All DB compatibility level is 100. The Collation of master/msdb is 'Latin1_General_CI_AS' and is different from collation of model/tempdb 'SQL_Latin1_General_CP1_CI_AS'.

    That is error you get if you mix collations in a query.

    The patch is the COLLATE clause:

    http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/

    The substantial solution is making all db-s on the server the same collation, if that is feasible.

    In the above case temporary tables are created with the default tempdb collation unless the collation explicitly declared on the table columns.

    To change column collation:

    http://www.sqlusa.com/bestpractices/changecollation/

    Collation is actually a column property. Database and server collation are just default values. When you change a database collation, only the default value changes. Table columns remain the same collation as they were before.

    Related articles:

    Consider Your Collations Carefully, Or Pay Later

    How to change the collation of TEMPDB

    Changing the collation of the tempdb database


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES




    Thanks for the reply....

    this error i am not receiving when im firing a query... i am receiving this error while checking properties of database through GUI.... so i can't use any of the fixes specified for query related.....

    i have gone through the articles that have given detailed method of rebuilding system db's... i'm just curious to know is there any other way other than rebuilding the system db's.......

    Thanks


    VVinayPrasad

    Wednesday, March 07, 2012 5:58 AM
  • You might have this problem with some of the older version of SSMS when you have a database set to AutoClose.  Which version of SSMS are you using?

    Old solution: Open the "Object Explorer Details" from the View menu (or use the key [F7]).  (Don't confuse the "Object Explorer Details" with the "Object Explorer").  On the "Object Explorer" tab (usually at the left), navigate to the Databases node.  On the "Object Explorer Details" window (at the right), right click on the list of properties (Name, Policy Health State, Recovery Model, etc.) to display the contextual menu and remove the Collation column from the list of available colums to be displayed.  Close and reopen SSMS and possibly that you will be OK.

    Wednesday, March 07, 2012 9:05 AM
  • All DB compatibility level is 100. The Collation of master/msdb is 'Latin1_General_CI_AS' and is different from collation of model/tempdb 'SQL_Latin1_General_CP1_CI_AS'.

    How did this happen? What is the collation of the application database?

    When you are using the GUI interface, that runs some scripts/sps which create a #temptable, hence the collation error. With SQL Profiler you can actually get the offending code.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES


    Wednesday, March 07, 2012 11:25 AM