March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi,
I am using the latest version Power BI Desktop.
I have a Direct Query to an SQL Database I have hosted on an Azure machine.
I am getting the following error when I try filtering the data:
Error Message:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.. The exception was raised by the IDbCommand interface.
Each table in SQL looks like they have the same collation "SQL_Latin1_General_CP1_CI_AS".
Any ideas on how to resolve this would be very helpful.
Many thanks
Michael
Solved! Go to Solution.
Hi @manderson,
There are three levels of collation in SQL Server: server collation, database collation, column collation.
In your scenario, if one column in involved tables has different collation as other columns, the above error will occur. I would recommend you use T-SQL to find out collation of database and table columns that imported in Power BI Desktop, and check if there is difference. There is an example for your reference, for more details, please review this blog.
SELECT DATABASEPROPERTYEX('AdventureWorks2014', 'Collation') GO /* Find Collation of SQL Server Database Table Column */ USE AdventureWorks2014 GO SELECT name, collation_name FROM sys.columns WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM sys.objects WHERE type = 'U')
Here is also a similar blog about the above error for your reference.
http://www.ashishblog.com/how-to-resolve-the-collation-conflict-and-how-to-check-collate-in-sql-serv...
Thanks,
Lydia Zhang
Hi @manderson,
There are three levels of collation in SQL Server: server collation, database collation, column collation.
In your scenario, if one column in involved tables has different collation as other columns, the above error will occur. I would recommend you use T-SQL to find out collation of database and table columns that imported in Power BI Desktop, and check if there is difference. There is an example for your reference, for more details, please review this blog.
SELECT DATABASEPROPERTYEX('AdventureWorks2014', 'Collation') GO /* Find Collation of SQL Server Database Table Column */ USE AdventureWorks2014 GO SELECT name, collation_name FROM sys.columns WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM sys.objects WHERE type = 'U')
Here is also a similar blog about the above error for your reference.
http://www.ashishblog.com/how-to-resolve-the-collation-conflict-and-how-to-check-collate-in-sql-serv...
Thanks,
Lydia Zhang
Thanks for your help Lydia @v-yuezhe-msft, that identified the columns that had incorrect collation no problem at all.
For any one else who has this problem, I used this post: https://msdn.microsoft.com/en-us/library/ms190920.aspx to change the collation to the correct one, using the code:
ALTER TABLE dbo.MyTable ALTER COLUMN CharCol varchar(10)COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL; GO
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.