Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. "

Question

I ve got the following error in one of our deployment databases (Deploymentserver1),

"An error has occurred during report processing.
Query execution failed for data set 'dsSalesData'.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. "

Select ...
from   ...
where #tempSales2.saleid in  (select saleid from #tempSales)   <Error line. Comparison between #tempSales with #tempSales2  >
//   #tempSales2 is a table created with database tables, #tempSales is a table created using varchar array

 When I replace the 'error line' as follows it works,

   'and (#tempSale2.saleid in  (select SaleId collate SQL_Latin1_General_CP1_CI_AS  from #tempSales))' 

   Issue here is that #tempsales content has 'Latin1_General_CI_AS' collation, when I forcibly set it to 'SQL_Latin1_General_CP1_CI_AS'  it worked.
In database (Deploymentserver1) properties it shows the 'SQL_Latin1_General_CP1_CI_AS' collation. so i wonder how come this temp table inherited 'Latin1_General_CI_AS' settings. But the machine's reigonal settings are set to Austrailian culture, which maps to the 'Latin1_General_CI_AS' collation. 
I assumed that it may inherited from windows , but in one another deployment server this works fine, which is identical to the 'DeploymentServer1'.
(We migrate reports from Deploymentserver2 to Deploymentserver1 )

This is how the #tepsales creates

CREATE TABLE #tempsales
 (
  saleId varchar(20), 
 ) 
 WHILE (CHARINDEX(',',@sales)>0)  -- @sales is an array of comma seperated varchar values.
 BEGIN
  INSERT INTO #tempsales (SaleId)
   VALUES (LTRIM(RTRIM(SUBSTRING(@saless,1,CHARINDEX(',',@sales)-1))))
  SET @sales = SUBSTRING(@sales,CHARINDEX(',',@sales)+1,LEN(@sales))
 END
 INSERT INTO #tempsales (saleId) VALUES (@sales)

Can anybody explain how this collation thing works related to temp tables, does it get inherited from server properties, or from the operation system settings ? What are the possible cases?, I need to figure out where it came from and fix that.

Please HELP!

Answer 1

When you create temp tables it will use the default collation of temp db and not the collation of database in which you are creating the temp table.

Now to resove your issue you need to specify the collation using collate keyword while creating temporary table.
I guess you can also use the database_default option in the COLLATE clause to specify that a column in a temporary table use the collation default of the current user database for the connection instead of tempdb.
Hope it helps.
Zainu

Answer 2

Zainu gave you some good information.

You might also want to read about my initial experiences with this kind of thing here:
http://bradsruminations.blogspot.com/2009/07/oh-yeah-collate-this.html

---

--Brad (My Blog)

Answer 3

Instead of
'and (#tempSale2.saleid in  (select SaleId collate SQL_Latin1_General_CP1_CI_AS  from #tempSales))' 

You can just do

'and (#tempSale2.saleid COLLATE DATABASE_DEFAULT in  (select SaleId  from #tempSales))' 

The advantage: much easier to remember! Also remember that collation is a column property. Not table, not database, not server!

Articles:

http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/

http://www.sqlusa.com/bestpractices/changecollation/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012