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.