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


  • 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.
      INSERT INTO #tempsales (SaleId)
       VALUES (LTRIM(RTRIM(SUBSTRING(@saless,1,CHARINDEX(',',@sales)-1))))
      SET @sales = SUBSTRING(@sales,CHARINDEX(',',@sales)+1,LEN(@sales))
     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!

    • Moved by Tom PhillipsModerator Thursday, November 05, 2009 9:06 PM TSQL Question (From:SQL Server Database Engine)
    Thursday, November 05, 2009 11:41 AM


  • 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.
    • Marked as answer by Zongqing Li Wednesday, November 11, 2009 7:53 AM
    Thursday, November 05, 2009 11:56 AM

All replies