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 SQL Server

Microsoft Dynamics NAV – “The following ODBC error occurred: Error: [Microsoft][ODBC Driver Manager] Invalid string or buffer length State ID: HY090”

—————————
Microsoft Dynamics NAV Development Environment
—————————
The following ODBC error occurred:

Error: [Microsoft][ODBC Driver Manager] Invalid string or buffer length
State ID: HY090
—————————
OK
—————————

When you try to read a Microsoft Dynamics NAV (formerly Microsoft Business Solutions Navision) BLOB Data Type field with an ODBC connection or simply to do a Backup of Navision show the following this error message:

Invalid string or buffer length

Cause:

When you use Microsoft Dynamics NAV BLOB field with Compressed Property to No and SubType Property to Memo is possible to write values from 4 bytes to 2 GB.

Solution:
Write something (like 4 spaces) in the field to cover the 4 byte in all the rows with blob field containing less then 4 bytes.

You can run a query in SQL Server like this:

SELECT *
FROM [TableName]
where DATALENGTH([ImageColumn]) < 4

Did my solution solve your problem? Leave a reply.