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

[Roseventura]

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!

[Anonymous]

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'

[Anonymous]

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.

[v-sihou-msft]

@Roseventura

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

Regards,

[Roseventura · Accepted solution]

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.

[Anonymous]

It is true, it really works!!!