cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alex_Rajkov Regular Visitor
Regular Visitor

Suddenly dataflow refresh error SqlDateTime overflow

Since this morning dataflow throws the following error while refreshing from MS NAV 13 via ODATA connection.

 

Invalid date,NAV ODATA TEST,Fehler,Verkaufsgutschriftszeile,12/08/2019 16:23:58,12/08/2019 16:24:28,Fehler,Error: An internal error occurred... RootActivityId = 415c20ec-6fd7-40e6-b6e3-0f8521044913.Param1 = SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. Request ID: 95a6922e-4408-56f7-17ec-82af235cc283.

 

No changes being made in the simple PQ script. Its just get the columns and define the column datatype.
Already checked the MS NAV database system time. Everything is just fine there. 

The dataflow worked just fine til last week.

In the dataflow options I (had to) change the region from Germany to English (United States). But tested reversing back to Germany also. No success.


The same refresh works just fine via PBI Desktop..

 

Help needed.

 

 

10 REPLIES 10
Community Support Team
Community Support Team

Re: Suddenly dataflow refresh error SqlDateTime overflow

Hi @Alex_Rajkov ,

According to error message, It seems like you have inputted wrong datetime parameter.
Current power bi not recognize datetime values who greater than 12/31/9999 or less than 1/1/1753. Please use valid datetime value to instead current one.

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
Alex_Rajkov Regular Visitor
Regular Visitor

Re: Suddenly dataflow refresh error SqlDateTime overflow

Hi Xiaoxing,

 

according to your response you didn't read my post.

You simply stated the error message.... 😞

 

KR
Alex


@v-shex-msft wrote:

Hi @Alex_Rajkov ,

According to error message, It seems like you have inputted wrong datetime parameter.
Current power bi not recognize datetime values who greater than 12/31/9999 or less than 1/1/1753. Please use valid datetime value to instead current one.

Regards,

Xiaoxin Sheng

Alex_Rajkov Regular Visitor
Regular Visitor

Re: Suddenly dataflow refresh error SqlDateTime overflow

After further tests, I assume the problem seems to be that there is some kind of query folding.

The problematic query doesn't contains any datetime statements. Just renaming of column names and changing the column type.

But the MS NAV 13 ODATA query contains a filter on the posting date. Posting_Date=FILTER(01.01.18..)

The filter works just fine in NAV and gives me the filtered table. Also in PBI Desktop I get the result.

 

Just PBI dataflow has an issue - and only with the beginning of this week.

1. How are we supposed to use dataflows, if the behavior changes without beeing informed of changes under the hood?

2. Why are the current bugs not solved?
a) A german user has to change the locale in dataflow to english, for it to work properly.
b) There is the date / datetime issue. Every time a column type is changed to just date and the entity is linked to another dataflow or loaded into PBI desktop the column type is datetime again...

 

Please solve these minor issues.

 

KR
Alex

Highlighted
marcusearly Regular Visitor
Regular Visitor

Re: Suddenly dataflow refresh error SqlDateTime overflow

@Alex_Rajkov 

I have the same issue happening. Suddenly getting this on a dataflow that has been working for months:

 

Error: An internal error occurred... RootActivityId = 710a9c86-c5ea-4a6d-be0d-2ff48508cbfa.Param1 = SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. Request ID: 0ff1c41f-fc08-454a-a2c0-95aeae3006a2.

 

If I convert DateTime columns to Text, it refreshes without issue. Something was messed up this last week in the Time format. It doesnt happen in the desktop model.

 

 

Alex_Rajkov Regular Visitor
Regular Visitor

Re: Suddenly dataflow refresh error SqlDateTime overflow

Not good, but good to hear I am not the only one.

I have a call with MS Support tomorrow and will post again if I have a solution.

 

Right now I fixed it by inserting replace value #date(1,1,1) with null for all date columns...

marcusearly Regular Visitor
Regular Visitor

Re: Suddenly dataflow refresh error SqlDateTime overflow

@Alex_Rajkov  I was able to get past this in the dataflow today. I had a few date/time fields that had "0217" rather than "2017".  This was never really a problem before and have been in the dataset for months. I corrected these prior to converting to date/time and the dataflow now refreshes. I wonder in the past if this would have come back as "0217" as the year... whatever change they put in has this now appearing as "217" which triggers the error.

Alex_Rajkov Regular Visitor
Regular Visitor

Re: Suddenly dataflow refresh error SqlDateTime overflow

I am curious, did you also change to the new dataflow enhanced compute engine?

Even after fixing that new problem, I ran into more problems with dataflows.

 

refresh of dependent dataflows not refreshing with the source dataflow. Refresh runs forever, dataflow wouldn’t open to edit, other never seen before error messages...

 

I deactivated it today and so far so good.

 

 

marcusearly Regular Visitor
Regular Visitor

Re: Suddenly dataflow refresh error SqlDateTime overflow

I believe we have turned on the new compute engine. At a global organization so I cannot turn off unfortunately.

marcusearly Regular Visitor
Regular Visitor

Re: Suddenly dataflow refresh error SqlDateTime overflow

@Alex_Rajkov  did turning off the enhanced compute engine solve your issue?

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 143 members 1,710 guests
Please welcome our newest community members: