Solved

Indicium Collation error

  • 15 June 2023
  • 2 replies
  • 49 views
Blommetje
Userlevel 5
Badge +12

Hi,

I have a task that runs fine on my system and my UAT. However, on another server I get this error in Indicium;

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

 

I’ve check the collations on the servers, and both DB’s have the same collation; SQL_Latin1_General_CP1_CI_AS

But the server settings; 

on the working server; SQL_Latin1_General_CP1_CI_AS

on the failing server; Latin1_General_CI_AS

Now, I see the difference, but I kinda expected Indicium to respect the Db’s collation settings. But I guess not? Also, I don’t think my client wants to change this on his uat server. 

Is there any way to get around is? 

Any help is appreciated!

Thanks, 

Blommetje 

 

 

icon

Best answer by Vincent Doppenberg 15 June 2023, 17:08

View original
This topic has been closed for comments

2 replies

V
Rank
Userlevel 6
Badge +4

Hello Blommetje,

Indicium does respect the collation of the database, in fact, Indicium never does anything to the collation itself and always defers to the database. I’m fairly certain that, when comparing a column to a string literal, SQL Server will collate the string literal to the collation of the column implicitly and so Indicium should not have to collate filter values.

I can only imagine three scenarios in which you get this issue:

  • Two columns with different collation settings on the database are being compared. It could be the case that Indicium does this comparison through a join statement. In this case only Indicium could fix the issue by collating one of the columns to the other, but Indicium currently has no knowledge of column collations. If the comparison is done in a view, expression or SQL prefilter, then the collation should be done by the developer in that SQL expression.
  • A column with a non-default collation is being compared to a temporary table column. Temporary tables will default to the default collation and similar to the point above, this will cause a collation conflict that should be solved by the developer.
  • You are doing a comparison in a view, expression or SQL prefilter where you are manually collating a column or a string literal to a different collation, which causes this conflict.

I could be wrong about this, but maybe you can shed a bit more light on the situation in which this error occurs (i.e. what does the query look like, what is the specific comparison that goes wrong and what are collations of the columns involved?).

I hope this helps.

Blommetje
Userlevel 5
Badge +12

Thank Vincent, 

I fixed it with these suggestions. It was in fact a #temptable created, which defaults to the server collation. 

I’ve added a collation to the column in case; 

CREATE TABLE #importedJsonData_employee (employee_number NVARCHAR(255)   COLLATE SQL_Latin1_General_CP1_CI_AS…

 

And this fixed it. NOT sure how this will behave on other servers.. if the collation differs. 

But i’ll work around that later.. for now basic solutions will do. 

Thanks for the help!

Blommetje 

 

Terms & ConditionsCookie settings