Categories
Microsoft SQL Server

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_100_CI_AS”

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_100_CI_AS” in the equal to operation.

in Italian

Impossibile risolvere il conflitto tra le regole di confronto “SQL_Latin1_General_CP1_CI_AS” e “Latin1_General_100_CI_AS” nell’operazione equal to.

Cannot resolve the collation conflict between…, SELECT command example

[sourcecode lang=”SQL”]
SELECT * FROM YourTableName1 One INNER JOIN YourTableName2 Two on One.YourColumnName = Two.OffendingColumn
[/sourcecode]

Cause:
You have two tables with different collation in the columns

Solution 1:

Use COLLATE Left or Right side of comparison in your SELECT command, for example:

[sourcecode lang=”SQL”]
SELECT * FROM YourTableName1 One INNER JOIN YourTableName2 Two on One.YourColumnName = Two.OffendingColumn COLLATE SQL_Latin1_General_CP1_CI_AS
[/sourcecode]

Solution 2:
Change collation of your OffendingColumn in one of your tables so you’ll have the same collation in for successive comparison
[sourcecode lang=”SQL”]
ALTER TABLE YourTableName
ALTER COLUMN OffendingColumn
VARCHAR(100) COLLATE Latin1_General_CI_AS NOT NULL
[/sourcecode]

Did my solution solve your problem? Leave a reply.

Categories
Dynamics NAV Microsoft Tips & Tricks

Tips & Tricks – Trim method in Microsoft Dynamics NAV (Microsoft Business Solutions-Navision)

My Tip & Trick help you to implement Trim function in C/AL for Microsoft Dynamics Nav.

Trim

Removes all white-space characters from the start and end of a string.

You can use the C/AL function DELCHR with the following syntax:

NewString := DELCHR(String [, Where] [, Which])

So, you can use the following code: 

String := DELCHR(String,'<>',' ');

TrimStart or LTrim

Removes all white-space characters from the start of a string.

String := DELCHR(String,'<',' ');

TrimEnd or RTrim

Removes all white-space characters from the end of a string.

String := DELCHR(String,'>',' ');

NOTE:
Since “spaces” are used as the default for Which, we can skip the second parameter and write code like this: 

String := DELCHR(String,'<>');

Do you think that my Tip & Trick is useful? Leave a reply.