3

I'm getting 'Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.' error.

The error is in a SP, I'm not specifying the collation anywhere, neither in the create table and when I use 

select t.name, c.*, c.collation_name 
from sys.columns c
left join sys.tables t
on c.object_id = t.object_id
order by c.collation_name

It says there is no 'Latin1_General_CI_AS' fields, all the fields are using the default collation. I'm returning a dataset but can't see where the error is. There are no joins. I am populating a temporary table, again, not specifying the collation yet it says there's an error. Any suggestions. Using SQL Server 2014.

MiscellaneousUser
  • 2,915
  • 4
  • 25
  • 44
  • Good suggestion but I have no triggers in the database. – MiscellaneousUser May 06 '16 at 11:46
  • 2
    *"I am populating a temporary table, again, not specifying the collation*" - If you create a temporary table, it will use the collation in tempdb, which may not match the collation of the database you are using. This could be the cause of the conflict. Try running `SELECT collation_name FROM sys.databases WHERE name IN ('tempdb', 'YourDatabase')` and see if you get two different collations – GarethD May 06 '16 at 11:48
  • On my dev machine, the SP works fine but on the production server it doesn't. Do you know how to find out what one the SP is using ? – MiscellaneousUser May 06 '16 at 11:49
  • Hmmm, I did as you advise Gaz, and indeed, I got two collations. Anyway of resolving that ? I'm on a shared server so might not be able. – MiscellaneousUser May 06 '16 at 11:59
  • You could explicitly define collation when creating the temp table within your procedure to avoid conflicts. Or you could use a table variable instead of a temp table (a table variable inherits collation from the current database). There should be no issue with either of these changes on a shared server. [This question](http://stackoverflow.com/q/23504023/1048425) addresses a similar issue – GarethD May 06 '16 at 12:06
  • Thats what I've done now, explicitly stated the Collation in the temp table creation. It works on both systems, dev and prod so am happy. – MiscellaneousUser May 06 '16 at 12:22

1 Answers1

3

Temporary tables use the collation of tempdb by default except if used in the context of a contained database.

You can specify COLLATE DATABASE_DEFAULT to have it use the collation of the current database.

CREATE TABLE #T
(
SomeColumn VARCHAR(50) COLLATE DATABASE_DEFAULT
)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845