Forum Discussion
Collation Error while reading data form SQL Endpoint of Lakehouse
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.
|
5 Replies
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 TeamI 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 proceduresYou "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 .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_01ASselect * from LH_01.STG.table_01 where modified_date = <some date>Result error :NON DEFAULT COLUMN COLLATION is not supported for SELECT INTO.
