Knowledge Base Article Number: Q211874

Question by: David Klein    Answered by: David Klein    Last Updated: 22/06/2007 6:30:42 PM

Spot an error? Please comment about this article

Why do I get the error 'Cannot resolve collation conflict for equal to operation' In my stored procedure?

Answer:
This will occur when your query attempts to join a user
table to a temporary table, and the tempdb database collation differs from the user
database collation (sp_helpdb will show you default database collation).  

To fix this, you can either:
1) Locate the query that is raising the error,
identify the join expression and use the COLLATE clause to tell SQL
which collation to use so that the query semantics aren't ambiguous.

OR

2) Change the collation of the temp db database to be consistent between the databases.

For example, we get the error when running the select in this query:

CREATE TABLE #ClientSummary
(
ClientID varchar(6) ,
CoName varchar(100),
AccountManager varchar(6),
CountOfInvoice int,
LongestDaysOS int,
[Name] varchar(255),
TotalInvoiceOSAmt money,
DatePromisedToPay datetime,
DateofLastReminder datetime,
DaysSinceLastReminder int,
ReminderTypeLastSent int,
AmountOfLastPayment money,
DaysSinceLastPayment int,
DateofLastPayment datetime,
RecommendedReminder int
)


    SELECT
        #ClientSummary.ClientID,  
        DateOfLastReminder = MAX(ClientDiary.DateCreated),
        DaysSinceLastReminder = DATEDIFF(day,MAX(ClientDiary.DateCreated), getdate())
    FROM
        ClientDiary INNER JOIN #ClientSummary
        ON ClientDiary.ClientID = #ClientSummary.ClientID
    WHERE
        ClientDiary.CategoryID LIKE 'DEBT-%'
    GROUP BY
        #ClientSummary.ClientID


Instead, use this for the select statement, with the COLLATE database_default
clause :

    SELECT
        #ClientSummary.ClientID,  
        DateOfLastReminder = MAX(ClientDiary.DateCreated),
        DaysSinceLastReminder = DATEDIFF(day,MAX(ClientDiary.DateCreated), getdate())
    FROM
        ClientDiary INNER JOIN #ClientSummary
        ON ClientDiary.ClientID = #ClientSummary.ClientID COLLATE database_default
    WHERE
        ClientDiary.CategoryID LIKE 'DEBT-%'
    GROUP BY
        #ClientSummary.ClientID

NOTE: You will have to recreate the tempdb database (or DTS the relevant user database into a blank database with the correct collection) if you want to do a join on a datetime field.

Keywords: Hot Tip - ASP.NET  SSW SQL Auditor  Hot Tip - SQL Server 2000    

New Search SSW Custom Support