Databases
StackOverflow2013
Postsdbo
POSQL collation conflict with temp table and procedure params coming from Delphi app
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL collation conflict with temp table and procedure params coming from Delphi app
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USSonOfGrey
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL collation conflict with temp table and procedure params coming from Delphi app
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL collation conflict with temp table and procedure params coming from Delphi app
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL collation conflict with temp table and procedure params coming from Delphi app
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAnother solution here: http://stackoverflow.com/questions/847636/how-can-i-resolve-my-collation-conflict-in-a-better-way?rq=1
      singulars
      1. POSQL collation conflict with temp table and procedure params coming from Delphi app
      1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload