DBAs - Do you avoid collation errors?

Last updated by Brady Stroud [SSW] about 3 years ago.See history

You don't want this error:

"120ClientInvoiceClientIDRequired.sql...Column 'dbo.Client.ClientID' is not of same collation as referencing column 'ClientInvoice.ClientID' in foreig..."

When you write a stored proc - it must work regardless of the users collation. When you are joining to a temp table - meaning you are joining 2 different databases (eg. Northwind and TempDB) they won't always have the same collation.

The reality is that you can't tell a user what collation to run their TempDB - we can only specify the collation Northwind should be (we don't even want to specify that - we want that to be their default (as per their server)).

Here is what you need to do:

 DateOfLastReminder = MAX(ClientDiary.DateCreated),
 DaysSinceLastReminder = DATEDIFF(day,MAX(ClientDiary.DateCreated),getdate())
 INTO #RecentReminderList
 ClientDiary INNER JOIN #ClientSummary
 ON ClientDiary.ClientID = #ClientSummary.ClientID COLLATE
 ClientDiary.CategoryID LIKE 'DEBT-%'
We open source. Powered by GitHub