Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fess440
Helper III
Helper III

Error returned: 'OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D - HOW TO DEBUG

Hi, I am running a sql query from Power BI which imports data from columns that have atleast one non-empty & not null data point from a table with ~600 columns. I am using SQL query directly because M takes too long. 

 

The query runs fine in SSMS & I see preview in query editor but I get the following error when I try to close & apply:

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D. 

 

Oddly, it works fine when I use the following query (SQL 2016) which can give me columns with empty values (which i call #1 for reference):

 

set @columnscountqry = REPLACE(stuff((SELECT + ',count(tbl.' + QUOTENAME(ic.COLUMN_NAME) + ') AS ' + QUOTENAME(ic.COLUMN_NAME) FROM INFORMATION_SCHEMA.columns ic where ic.TABLE_NAME = 'tblexample' FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, ''), '> 0', '>0')

 

I run the following to get the columns that I want which are columns that have atleast one non-empty & non null value (#2 for reference):

 

set @columnscountqry = REPLACE(stuff((SELECT + ',SUM(IIF(LEN(RTRIM(fb.' + QUOTENAME(ic.COLUMN_NAME) + ')) > 0, 1 ,0)) AS ' + QUOTENAME(ic.COLUMN_NAME) FROM INFORMATION_SCHEMA.columns ic where ic.TABLE_NAME = 'tblexample' FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, ''), '> 0', '>0')

 

I use these column names in additional SQL script to get the output that I want. It works fine in SSMS & the results are accurate. I have not given the full SQL script as the rest of the script is the same.

 

I think I should compare the data types that are getting assigned in power query between #1 & #2 as #1 is getting imported without error. Interestingly, #2 produces less columns as expected & all of #2 columns get also pulled in #1. I do not know the difference between the data types that are getting assigned to them however as there are nearly ~300 columns to check.

 

Can you please let me know how I can get a list of columns & the data types that are getting assigned to them in query editor? It's not possible to do manually as nearly ~300 columns are extracted.

 

Any other ideas?

 

Thanks a lot.

6 REPLIES 6
fess440
Helper III
Helper III

i think the issue is that some date fields have value 'NULL' in them with dates & the table schema shows these columns are non nullable. i'd like to try to change all columns to nullable to get around this problem. - i did a thorough check of the nullable property & that's not causing the issue. please see new comment below.

 

could somebody please let me know the m query to do so, or direct me to relevant examples please? thanks a lot.

@MarcelBeug 

i've come to learn that Power BI is detecting data type based upon the 1st 200 rows. i'm seeing errors generated on random now for either #1 or #2 so it's likely because the SQL result set is not ordered.

 

i can't order the set because each row represents a set of info & there's no guarantee it will always work. is there a way to pull the data types from the SQL information schema & impose that on the power bi table using M query?? 

 

 

i am using the following function to change the data types of the columns:

 

(table as table, schema as table) as table =>
let
existingColumns = Table.ColumnNames(table),
removeMissing = Table.SelectRows(schema, each List.Contains(existingColumns, [Name])),
primativeTransforms = Table.ToRows(removeMissing),
changedPrimatives = Table.TransformColumnTypes(table, primativeTransforms)
in
changedPrimatives

 

schema has the column names with type text only for testing purposes

 

removeMissing is producing the required table. i am getting an error at primativeTransforms:

An error occurred in the ‘’ query. Expression.Error: We cannot convert the value "text" to type Type.
Details:
Value=text
Type=[Type]

 

i am new to m language. could i get some help please? thanks

@MarcelBeug 

Hi @fess440 ,

According to the error information, it seems to be data type conversion error... Could you please provide the applied codes in Advanced Editor for the query with error and the related screen shots of query which get error? Thank you.

In addition, please refer the content in the following links and check if they can help you resolve the problem.

0x80040E4E Error - OLE DB or ODBC

OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D | Power bi Refresh Error

Data refresh error: OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D 

Best Regards

Rena

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

@v-yiruan-msft thanks for your response. to recap, i ran a dynamic sql script from power bi to pull columns that had atleast 1 data point that was not empty or null from a set of ~600 columns & i pulled nearly ~300 columns. i had to do this for several entities & the results were different for each entity. power bi was not detecting data type accurately for some columns as some columns had null values & dates, for example, spread across many rows. i finally managed to resolve the issue by using M query to change the data type of columns given unique column names & data types from the database information schema after much trial & error. i'm dealing with relatively "big data" & it would be wonderful to have solutions out of the box to do same especially when  manual intervention is impractical. do you know of something automated that can achieve same, or would you recommend starting an idea? thanks again for your time. cheers.

Hi @fess440 ,

Did you enable the option Automatic data type detection (go to File->Options and Settings->Options, then Data Load-> Type detection)?

  • The automatic data type detection step uses a sampling or preview of each source column to determine the appropriate type. If this sample doesn't reflect the full or future set of values for the column the data type selected may be incorrect.
  • Automatic type detection is not used with structured relational database systems such as SQL Server.
  • If enabled, this step only applies to unstructured data sources such as flat files and Excel workbooks.

Automatic data type detection.JPG

Best Regards

Rena

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.