title: SQL collation conflict with temp table and procedure params coming from Delphi app
body: <p>I've been working with MS SQL a few years now, and I've never encountered anything like this on my previous job. But where I work now, I got an error that I'd really like to know the cause from.</p>
  
  <p>I made a stored procedure and called it in my Delphi 5 (yeah I know) app with some parameters. This worked fine on two databases (copies from different times). But now I tried it on another DB (again a copy), but it gave me the following error:</p>
  
  <pre><code>Cannot resolve the collation conflict between "Latin1_General_CI_AS" and 
  "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
  </code></pre>
  
  <p>I got this by creating a temp table and then trying to insert some data. I'm not even joining. And the funny thing is: when I remove the whole WHERE clause, it works. When I leave it (although it only compares parameters with one table), it fails.</p>
  
  <pre><code>create table #TOP (EDAID int, ParentID char(30), ChildID char(30),
      Position int, OrgQty_modified_manually bit)
  </code></pre>
  
  <p>This fails:</p>
  
  <pre><code>insert into #TOP
  select EDAID, ParentID, ChildID, Position, OrgQty_modified_manually
  from EDA_SOBOM
  where OrderNr = @OrderNr
      and Position = @Position
      and LN = @LN
      and DL = @DL
      and rtrim(ChildID) = @CurrentPart
      and rtrim(ParentID) = @ParentID
  </code></pre>
  
  <p>This works:</p>
  
  <pre><code>insert into #TOP
  select EDAID, ParentID, ChildID, Position, OrgQty_modified_manually
  from EDA_SOBOM
  </code></pre>
  
  <p>The procedure parameters are declared like this:
      @PartID char(30), @Position int, @OrderNr char(8),
      @LN char(2), @DL char(2), @ParentID char(30), @Modified bit output</p>
  
  <p>I found a solution here: <a href="https://stackoverflow.com/questions/1607560/cannot-resolve-the-collation-conflict-between-sql-latin1-general-cp1-ci-as-and">Cannot resolve the collation conflict between &quot;SQL_Latin1_General_CP1_CI_AS&quot; and &quot;Latin1_General_CI_AS&quot; in the equal to operation</a>.</p>
  
  <p>So I added this right after the CREATE:</p>
  
  <pre><code>ALTER TABLE #TOP
    ALTER COLUMN ParentID
      VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
  
  ALTER TABLE #TOP
    ALTER COLUMN ChildID
      VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
  </code></pre>
  
  <p>And that made the whole thing work again...but I don't get it why the WHERE clause with only parameter comparisons fails...<strong>can parameters have collation too?</strong></p>
  
  <p>DB has collation SQL_Latin1_General_CP1_CI_AS.
  Table EDA_SOBOM also has collation SQL_Latin1_General_CP1_CI_AS on the char columns.
  I found this by the following query:</p>
  
  <pre><code>SELECT col.name, col.collation_name
  FROM sys.columns col
  WHERE object_id = OBJECT_ID('EDA_SOBOM')
  </code></pre>
  
  <p>Is there another place where collation can be set apart from the DB level and column level?</p>
  
  <p>I wonder what's going on...</p>