Collation Error while reading data form SQL Endpoint of Lakehouse

[Umasankar_Sane]

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.

[Umasankar_Sane]

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 

[lbendlin]

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 .

[Umasankar_Sane]

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

    AS

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

Result error :

NON DEFAULT COLUMN COLLATION is not supported for SELECT INTO.

[Tharunkumar]

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

[Anonymous]

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