cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manderson Frequent Visitor
Frequent Visitor

Couldn't load the data for this visual - Exception raised by the IDbCommand interface

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

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Couldn't load the data for this visual - Exception raised by the IDbCommand interface

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Moderator v-yuezhe-msft
Moderator

Re: Couldn't load the data for this visual - Exception raised by the IDbCommand interface

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

manderson Frequent Visitor
Frequent Visitor

Re: Couldn't load the data for this visual - Exception raised by the IDbCommand interface

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  

 

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors