Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2013-05-23 : 10:54:32
|
| We sometimes get collation errors with temp tables such as:Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Our policy is that we can control the Collation of our Database but we can not control the collation on the Server, our customer does. So if our customer sets the collation to something other than our Database, we can get these errors.We have been solving this by specifying the collation on every varchar temp table column. We are wondering what you believe is the best method to solve this issue? For example, I heard another way is to specify the collation on the queryies joins. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-23 : 11:05:49
|
| Would need to know a lot more about what you do.If you're importing data from the customer, change the collation on the way in.Temp tables are no different than any other tables when it comes to resolving collation conflicts.Specifying collation on joins is common where there are conflicts. |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-05-23 : 11:10:58
|
| We provide an application for customers. If their server has the same collation as our Database, we have no problems.Just trying to find what the best method for dealing with this, with regards to temp tables, or as you say any table.Is there any other option besides, specifying it on the column declaration or specifying it on the join? I wish there was an option, like: assume Database collation on all joins as a default. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-23 : 11:15:51
|
| Ahhh, I think I see now.Your database gets deployed to their server? In this case, it is THEM encountering the conflicts not YOU right? I'll tell them we only support such and such collation. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-23 : 11:20:23
|
| The issue, as I understand it, is that when the collation of the database differs from that of the server, you run into problems.As you perhaps know, if you create a temp table (which gets created in tempdb), by default, the collation will be the server collation (which should be the collation in MSDB).You can override that by explicitly specifying a collation during creation of the temp table. Given that, one way would be modify your scripts that create temp tables to specify the collation as "database_default". When you do that, temp tables use the collation of the current database (your database), rather than the collation of MSDB. That is the most portable solution that I can think of. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-23 : 11:25:41
|
Ahh, good catch James.Another workaround would be to not use #temp tables just use regular tables and don't forget ot drop 'em  |
|
|
|
|
|
|
|
|
Best way to handle collation errors