Forum Discussion
Getting the dreaded OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D error during refresh.
Here are the 3 tables included in my PBX:
- Calendar table – refreshes without incident
- Invoices 2016 – refreshes with incident
- 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:
- 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).
- I copied the code in the Advanced Editor from Invoices 2016 and put it in Invoices 2017.
- I tried doing the refresh from both Data view and Report view.
- I deleted the 1:many relationship between Calendar and Invoices 2017.
- 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!
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.
8 Replies
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.
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'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.
I got this error because I changed data type of year in calander table,
from whole number to date and format to 2001 (yyyy) ..
restore previous data type fixed the issue.
I can't believe that worked, but it did. I changed a serial-date whole-number column in the Report View to a date column and that was messing everything up. Thank you for posting this!
