Forum Discussion
Responsive ResidentGetting the dreaded OLE DB or ODBC error: Exception from HRESULT: 0x80040E1D error during refresh.
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.