cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver IV
Resolver IV

Getting the dreaded OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D error during refresh.

Here are the 3 tables included in my PBX:

  1. Calendar table – refreshes without incident
  2. Invoices 2016 – refreshes with incident
  3. Invoices 2017 – receives this error every time I try to refresh

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

 

All my files are TXT files.  The “incoming” columns in both Invoices 2016 and Invoices 2017 are identical in both column name and data type.  Invoices 2016 has 5 measures and Invoices 2017 has 7 measures and 2 calc columns.

 

I don’t really need to refresh the Calendar and Invoices 2016 tables daily because that data is static.  However, every day I get the dreaded OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D error when I try to refresh Invoices 2017.  I don’t have any blank columns or empty cells that exist in a key column.

 

The Calendar table has a 1:many relationship to both Invoices 2016 and Invoices 2017.

 

Here’s what I’ve tried so far to no avail:

  1. In Advanced Editor for Invoices 2017, I removed the “Columns=35” (I tried this before on a different report and that fixed it). Btw, there ARE 35 columns.  I even tried changing it to 37 to account for the 2 calc columns (don’t know if that was necessary but it didn’t help anyway).
  2. I copied the code in the Advanced Editor from Invoices 2016 and put it in Invoices 2017.
  3. I tried doing the refresh from both Data view and Report view.
  4. I deleted the 1:many relationship between Calendar and Invoices 2017.
  5. I looked through and tried several other suggestions on this board.

 

The only thing that does work is if I create a duplicate of Invoices 2017, move my measures to the new table, delete the old table and refresh.  Only then will it refresh successfully.  But just once!  The next day, I get the error again.

 

I can’t keep going through these gyrations every day.  I feel like I’m not getting at the root cause of the problem, instead I’m only putting a temporary band aid until tomorrow.

 

HELP!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver IV
Resolver IV

Re: Getting the dreaded OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D error during refres

After much trial and error, I finally figured out what was causing the problem.

 

The invoice dates in my source file (TXT) are formatted as serial dates:     42737     = 1/2/2017

 

This is intentional.  When I first set up the table, I changed the formatting of the serial date in Invoices 2017 to be 2017-01-02.  I guess PowerBI doesn't like it when you reformat dates and then try to refresh a table (which makes no sense to me).  However, I found that as soon as I changed the formatting back to a serial date, the table refreshes without incident.

 

What I did to get around this is I created another column called "Formatted Invoice Date" and use that for appearances.

 

Note:  The dates in my Calendar and Invoices 2016 were formatted as serial.  I never changed them which is why I never had a problem with refreshing those tables.

View solution in original post

5 REPLIES 5
Highlighted
Microsoft
Microsoft

Re: Getting the dreaded OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D error during refres

@Roseventura

 

Can you enable the trace and share the detail error message?

 

Regards,

Highlighted
Resolver IV
Resolver IV

Re: Getting the dreaded OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D error during refres

After much trial and error, I finally figured out what was causing the problem.

 

The invoice dates in my source file (TXT) are formatted as serial dates:     42737     = 1/2/2017

 

This is intentional.  When I first set up the table, I changed the formatting of the serial date in Invoices 2017 to be 2017-01-02.  I guess PowerBI doesn't like it when you reformat dates and then try to refresh a table (which makes no sense to me).  However, I found that as soon as I changed the formatting back to a serial date, the table refreshes without incident.

 

What I did to get around this is I created another column called "Formatted Invoice Date" and use that for appearances.

 

Note:  The dates in my Calendar and Invoices 2016 were formatted as serial.  I never changed them which is why I never had a problem with refreshing those tables.

View solution in original post

Highlighted
New Member

Re: Getting the dreaded OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D error during refres

It is true, it really works!!!

Highlighted
Anonymous
Not applicable

Re: Getting the dreaded OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D error during refres

I'm getting this error using when I use a subselect in Power BI, using a DB2 connection,  with the microsoft driver to our IBM i.

It works fine if I run the same SQL on the IBM i itself, and  also without the subselect in Power Bi.

If I replace the subselect with a 'hardcoded' date it works fine also in Power Bi.

 

What's going on ?

 

Any ideas please ?

 

select '20' CONCAT CAST(t2.cxyear2 as VARCHAR(2)) CONCAT '-'
CONCAT digits(t2.cxweek2) as SLYRWK,
t2.cxyear2,t2.cxweek2,
t1.slinvn,t1.slorno,t1.slinvd,
t1.slqtyi,t1.slgdsv,t1.slcstv,t1.slcstv,
t1.slacon,t1.slprod,
t3.acsln, substring(t4.frear,11,10) as subleddsc
from yglivdta.salanl as t1
join yglivdta.calmasx as t2
on t1.slyear = t2.cxyear and t1.slweek = t2.cxweek
and t2.cxyear2 >= 18 and t2.cxweek2 > 00
join cgasr6v0d1.accbal as t3
on t3.ledgr = 'S' and t3.coynr = '001'
and t1.slacon = t3.aconr
join cgasr6v0d1.param as t4
on t4.rekey = concat('0013', cast(t3.acsln as varchar(2)))
where ('20' CONCAT DIGITS(t2.cxyear2) CONCAT '-' CONCAT
DIGITS(t2.cxweek2)) <=

(select ('20' CONCAT DIGITS(a.cxyear2) CONCAT '-' CONCAT
digits(a.cxweek2))
from yglivdta.calmasx as a
where a.cxweek2 <> 0 and cxyear2 <> 0


and CURRENT_DATE >= timestamp_format(char(a.cxdate2 + 20000000),'YYYYMMDD')
and CURRENT_DATE <= timestamp_format(char(a.cxdate2 + 20000000),'YYYYMMDD'

Highlighted
Anonymous
Not applicable

Re: Getting the dreaded OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D error during refres

I eventually fixed the issue myself by adding an identical datasource (fortunately there is only one table), deleteing the old one and then re-adding all the measures and calculated columns that I had pasted earlier into notepad. The linkages from the fields to the visuals were all still in place when I deleted the old datasource. 

 

Strange, but it's now working.

 

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors