SQL SERVER – Cannot Resolve Collation Conflict For Equal to Operation

Cannot resolve collation conflict for equal to operation.

SQL SERVER - Cannot Resolve Collation Conflict For Equal to Operation collationconflict-800x206

In MS SQL SERVER, the collation can be set at the column level. When compared to 2 different collation columns in the query, this error comes up.

SELECT ID
FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col = AccountsTable.Collation2Col

If columns ItemsTable.Collation1Col and AccountsTable.Collation2Col have different collation, it will generate the error “Cannot resolve collation conflict for equal to operation“.

Solarwinds

To resolve the collation conflict add the following keywords around “=” operator.

SELECT ID
FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT
= AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT

Watch this quick video for resolution:

Collation can affect the following areas:

  1. Where clauses
  2. Join predicates
  3. Functions
  4. Databases (e.g. TempDB may be in a different collation database_default than the other databases some times)

Checkout following video how collation can change the order of the column:

Just like collation, there is another problem with SQL Server and that is related to Parameter Sniffing, I have written a detailed blog post about how the parameter sniffing can be resolved in SQL Server.

Here are some really good blog posts which you can read about parameter sniffing and how you can avoid the problem related to it.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , , ,
Previous Post
SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice
Next Post
SQL SERVER – LEN and DATALENGTH of NULL Simple Example

Related Posts

381 Comments. Leave new

  • Thanks… It solved my problem

    Reply
  • Let me thank you in 2009.02.09
    Great Solution.

    Reply
  • Kasun Kularathne
    March 5, 2009 1:05 pm

    Hi…. thank u so much …it’s really work for me :-)

    Reply
  • Genius!

    Reply
  • Thanks a lot!!!!

    Reply
  • Helped me too when I was doing an update query.

    I didn’t even know what collation was before this!

    Reply
  • I get this error, when I add the clause, Expression type numeric is invalid for COLLATE clause.

    I have added as :

    ON tablename.Columnname COLLATE DATABASE_DEFAULT = tablename.columnname COLLATE DATABASE_DEFAULT

    Reply
  • krishna murthi b
    March 30, 2009 2:02 pm

    Worked for me. Simple solution.

    thanks,
    Murthi

    Reply
  • Sriram Prasad
    April 7, 2009 1:54 am

    Thanks,Simple Solution.

    But how can i make the two tables to have same collation?
    I don’t want to have different collation for tables.

    How can i know a table/column’s collation?

    Reply
  • God bless you. your solution is really good.

    Thanks a lot.

    Reply
  • thanks!

    worked like a charm!

    Reply
  • Its simply great. Thanks.

    Reply
  • Thank you. Your solution saved me from hour of work.

    Reply
  • Thanks! You saved my life!

    Reply
  • Awesome… quick easy answer, saved us some time this morning! Thanks

    Reply
  • Hey, we have one ready applicaion…it’s working fine in most of the customer but only one customer getting this error.

    [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot resolve collation conflict for equal to operation.

    How to resolve this error with out chnage in code?
    how to resolve this issue using SQL SERVER enterprise manager?

    Regards / Sandeep

    Reply
  • You saved my day. Thank!

    Reply
  • Thanks a lot.
    I had this problem and with this solution my query working fine.
    A litlle diference between collates from diferent databases make this error.
    Theres a one thing that I have doubt.
    In my case I have 2 dbo’s
    The firts have this COLLATE
    SQL_Latin1_General_CP1_CI_AS
    The other have this
    Latin1_General_CI_AS

    Whats the diference?
    And theres a way to choose the right COLLATE in my query?

    Sorry my english, I am brazilian…
    Thanks again

    Reply
  • I have collation error -‘Cannot resolve collation conflict for equal to operation’ when calling a function that returns a table. The function is as below:
    CREATE FUNCTION [dbo].[Split]
    (
    @RowData nvarchar(2000),
    @SplitOn nvarchar(5)
    )
    RETURNS @RtnValue table
    (

    Id int identity(1,1),
    Data NVARCHAR(10) NULL
    )
    AS
    BEGIN

    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
    Insert Into @RtnValue (data)
    Select
    Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
    Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
    END

    Then i added the server’s collation name as :

    RETURNS @RtnValue table
    (

    Id int identity(1,1),
    Data NVARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    )

    Then it is working fine. But i would like to know how to make this dynamic so that everytime the server’s collation property changes, i need not change the function.

    Reply
  • for the above problem, i tried using database_default but it threw the same error. Is there anything like server_default?

    Thank you for the help.

    Reply

Leave a Reply

Menu