Collation is a wonderful thing. It’s what makes sure that Greek people can see their data in a Greek order, and Germans can see theirs in a German order. It helps us determine whether data should be case sensitive, or whether those little dots and circles that Swedes use really matter.
But if you have a database that has different collation settings to the SQL instance it’s on, it can be a pain.
Suppose you have an instance of SQL Server, and you decide to restore a database on it. It may be an upgrade, it may be part of a consolidation exercise. Regardless of the reason, it turns out that the instance’s default collation is not the same as the instance that the database was on previously. This will affect the tempdb database.
So your application is ticking along nicely on the new server, until such time as the application needs to create a temporary table. This gets created in tempdb of course. Like most T-SQL code, the application doesn’t specify the collation for this temporary table, and the columns pick up tempdb’s default collation. Ah. Now as soon as you try to perform a join between this temporary table and a table in your application’s database, you get a collation error:
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between … in the equal to operation.
And you find yourself needing to change code, or change the SQL installation, or something to be able to resolve this.
But you just want the error to go away. You want it to just pick a collation, maybe the one from the column on the left of the comparison, maybe the one from the right. So long as the error goes away, you may not be all that interested. Chances are it’s just the difference between a Windows collation and a SQL collation, and the differences aren’t significant enough for you to care (I know, there are plenty of cases where you really do care – in which case you probably don’t have so much frustration with this as the rest of us).
Right now, there is no way of making that error go away. But I’m suggesting to Microsoft that there be a database-level option (or an instance-level option, or both – I don’t mind how it gets implemented), that will detect a collation error in a query and handle it in an appropriate way. After all, a collation error is detected before the query is executed, so changing the way it’s handled should be easy enough.
If you think this is worth voting for, then we might be able to see it implemented, and collation errors may become a thing of the past. They shouldn’t be ignored completely – I just don’t want to be the victim of coding that doesn’t specify the collation.
Vote at: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=324910
This Post Has 2 Comments
I encountered such issue several times. it is realy frustrating. Once when one of our customers ran some scripts to update their DB system, a script always failed. I was sure that all scripts have been tested on our server and they worked properly. the error message was definitely as same as the one mentioned above.
hopefully it will be solved in any manner.
i am in the middle of hell with one of these errors. I guess I have to rebuild the master database which is actually hosted by a provider and the databases where created by someone we hired that does not speak english. I am being blamed for this, but I cannot understand how the collation got changed in the first place. I searched the hundreds of sprocs and there is no colate commands. I did connect with visual studio but that should not make a difference. Whatever happened in our scenario this situation without question should at minimum throw a warning.