topic Re: Collation Error while reading data form SQL Endpoint of Lakehouse in Data Engineering

Collation Error while reading data form SQL Endpoint of Lakehouse

Umasankar_Sane — Tue, 05 Nov 2024 09:29:07 GMT

We are trying to read data from Lakehouse using Stored procedure . Transform in SP and store it in warehouse . It was working fine when we had warehouse with collation type case sensitive . But getting error for the same while reading data with cases in insensitive warehouse from SQL analytics end point of lake house.

NON DEFAULT COLUMN COLLATION is not supported for SELECT INTO.

Cannot resolve the collation conflict between "Latin1_General_100_BIN2_UTF8" and "Latin1_General_100_CI_AS_KS_WS_SC_UTF8" in the equal to operation.

Re: Collation Error while reading data form SQL Endpoint of Lakehouse

Anonymous — Wed, 06 Nov 2024 02:33:26 GMT

Hi @Umasankar_Sane

Not sure if the following solutions from SQL Server would work or not. It seems the solutions require you to set the collation for every column. You may give this a try.

sql server - SQL change field Collation in a select - Stack Overflow

t sql - Can I specify the collation when performing a SELECT INTO - Stack Overflow

However the column collation documentation seems not applied to Fabric currently. And this thread also mentions this limitation. So above solutions might be not supported. I haven't tested it, to be honest.

At the moment, the collation option in Fabric is only warehouse-wide and cannot be changed once the warehouse is created. How to: Create warehouses with case-insensitive (CI) collation - Microsoft Fabric | Microsoft Learn

Best Regards,
Jing
Community Support Team

Re: Collation Error while reading data form SQL Endpoint of Lakehouse

Umasankar_Sane — Wed, 06 Nov 2024 06:07:56 GMT

I have created warehouse with collation case insensitive

Now I am trying to read data from lakehouse endpoint in warehouse. Simple SELECT statements works fine with out issues.

But I try to join this data with Warehouse data it gives collation errors. Reading each column with collation mentioned is not option for me as I have multiple places where we read data from Lake house end point and join with warehouse data as part fo transformations in Stored procedures

Re: Collation Error while reading data form SQL Endpoint of Lakehouse

lbendlin — Wed, 06 Nov 2024 06:55:07 GMT

You "only" need to cast the collation on the join column.

I would still consider this a bug. If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .

Re: Collation Error while reading data form SQL Endpoint of Lakehouse

Umasankar_Sane — Wed, 06 Nov 2024 07:04:21 GMT

Thank you for the response .
I will try for Join columns . Major issue I had is with reading data from Lake house and creating temp table or cte in warehouse for further usage of data in stored procedures .
Example below :

CREATE TABLE temp_01

select * from LH_01.STG.table_01 where modified_date = <some date>

Result error :

NON DEFAULT COLUMN COLLATION is not supported for SELECT INTO.

Re: Collation Error while reading data form SQL Endpoint of Lakehouse

Tharunkumar — Thu, 30 Jan 2025 08:15:53 GMT

Hi, I too facing the same issue now. Can anyone please provide solution ?