SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Best way to handle collation errors
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 05/23/2013 :  10:54:32  Show Profile  Reply with Quote
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.

Edited by - denis_the_thief on 05/23/2013 11:54:14

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/23/2013 :  11:05:49  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 05/23/2013 :  11:10:58  Show Profile  Reply with Quote
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.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/23/2013 :  11:15:51  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 05/23/2013 :  11:20:23  Show Profile  Reply with Quote
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.

Edited by - James K on 05/23/2013 11:21:19
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/23/2013 :  11:25:41  Show Profile  Visit russell's Homepage  Reply with Quote
Ahh, good catch James.

Another workaround would be to not use #temp tables just use regular tables and don't forget ot drop 'em
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000