ODBC: ERROR [22007] [Microsoft][Support] (40490) Invalid date, time or timestamp value.

[Anonymous]

Hi All,

I'm wondering if someone can help me with this issue. I have set daily refresh on my report and it's been working fine for the past 5-6months, until this week I recieved scheduled refresh failure notice with the error message : ODBC: ERROR [22007] [Microsoft][Support] (40490) Invalid date, time or timestamp value. I have four tables in this report and all are coming from snowflake which all are using the same date source. 

For trouble shooting I created four reports each contain on of the four tables and refresh the data. Only one table is giving me trouble, but how is this possible if all tables are using the same dates? I have also tried refreshing the report with powerbi desktop and it gives me no issues. Does that mean the issue is not within the dataset but from powerbi server? Sometimes if I refresh the report multiple times it will succeed but most of the time it's giving me the same error message.

Is there a way of ignoring the ODBC error since I'm confident that all my date values are valid? 

[Icey · Accepted solution]

Hi @Anonymous, @pankajbirle, @Anonymous, @cfields22,

Are you using Power BI Service or Power BI Report Server? I guess you are using Power BI Service. Right?

If so, I need to confirm some details:

1. Is the issue with dataset or dataflow?
2. Is the dataset/dataflow refreshing stuck or running for long duration than before?

If your issue is with dataflow, the same issue has been submitted internally(#ICM: 273691054). Please be patient to wait for fixing. If there is any news, I will update it here.

If your issue is with dataset, there is currently no issue internally that is exactly the same as your error message. But there is an issue about snowflake dataset refreshing stuck or running for long duration than before(#ICM: 272592736). And there is an workaround, please check if it also work in your scenario:

Two options

1. Run SHOW PARAMETERS LIKE 'ODBC_QUERY_RESULT_FORMAT'

1. See if this option says value is "ARROW"
2. If ARROW, run ALTER USER SET ODBC_QUERY_RESULT_FORMAT='JSON'

2. If that did not work, contact Snowflake support to set ODBC_TREAT_DECIMAL_AS_INT=false in the server properties.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Matt-Butler]

Hello,

I have been having this same issue, has there been any updates?

Snowflake DWH using Power BI Dataflows in PPU.

Most of the time this works fine, every now and then i get that error (pasted below).

I have one dataflow that works in dev, but after publishing via pipelines to test does not work at all. 

i am able to use Power BI Desktop and load the table correctly. 

have made the Snowflake setting change "SHOW PARAMETERS LIKE 'ODBC_QUERY_RESULT_FORMAT'" to JSON, but no difference. 

I ahve create the dataflow directly in my test workspace and it also errors.

HELP!!!

Error: Data Source Error : DataSource.Error: ODBC: ERROR [22007] [Microsoft][Support] (40490) Invalid date time or timestamp value. DataSourceKind = Snowflake DataSourcePath = ***************** OdbcErrors = #table({"SQLState" "NativeError" "Message"} {}). RootActivityId = 280443ae-518b-4b27-91c9-447acfab65a7.Param1 = DataSource.Error: ODBC: ERROR [22007] [Microsoft][Support] (40490) Invalid date time or timestamp value. DataSourceKind = Snowflake DataSourcePath = ****************** OdbcErrors = #table({"SQLState" "NativeError" "Message"} {}) Request ID: 50dafb41-c550-47be-a2d8-5c4134300f59.

[nathangriffiths]

We are still encountering this issue as of 3/3/22

If we run SELECT CURRENT_CLIENT() in Power BI it still returns 2.24.1 which is the old, pre-fix version of the Snowflake ODBC driver. We think Power BI needs to be using the 2.24.2 version that was released *four months ago* for this issue to be resolved.

[vidhiarora095]

Hi all,

I am having a similar issue. I am using Power Bi Service and a dataset. 

I use the combination of snowflake tables and an excel sheet for my dataset. The dataset had been on schedule refresh for months and had been refreshing fine. However, since the last week, I keep getting the same error, and it either times out or fails. 

I have just given your solution on snowflake a try. Hopefully, this fixes the issue. 

Thanks, 

Vidhi

[Icey · Accepted solution]

Hi @Anonymous, @pankajbirle, @Anonymous, @cfields22,

Are you using Power BI Service or Power BI Report Server? I guess you are using Power BI Service. Right?

If so, I need to confirm some details:

1. Is the issue with dataset or dataflow?
2. Is the dataset/dataflow refreshing stuck or running for long duration than before?

If your issue is with dataflow, the same issue has been submitted internally(#ICM: 273691054). Please be patient to wait for fixing. If there is any news, I will update it here.

If your issue is with dataset, there is currently no issue internally that is exactly the same as your error message. But there is an issue about snowflake dataset refreshing stuck or running for long duration than before(#ICM: 272592736). And there is an workaround, please check if it also work in your scenario:

Two options

1. Run SHOW PARAMETERS LIKE 'ODBC_QUERY_RESULT_FORMAT'

1. See if this option says value is "ARROW"
2. If ARROW, run ALTER USER SET ODBC_QUERY_RESULT_FORMAT='JSON'

2. If that did not work, contact Snowflake support to set ODBC_TREAT_DECIMAL_AS_INT=false in the server properties.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Icey]

Hi @Anonymous , @robemie_0119 , @pankajbirle , @Anonymous , @vidhiarora095 ,

Update:

The ETA for fix is 12/31/2021.

---

@Icey wrote:

If your issue is with dataset, there is currently no issue internally that is exactly the same as your error message. But there is an issue about snowflake dataset refreshing stuck or running for long duration than before(#ICM: 272592736). And there is an workaround, please check if it also work in your scenario:

Two options

1. Run SHOW PARAMETERS LIKE 'ODBC_QUERY_RESULT_FORMAT'

1. See if this option says value is "ARROW"
2. If ARROW, run ALTER USER SET ODBC_QUERY_RESULT_FORMAT='JSON'

2. If that did not work, contact Snowflake support to set ODBC_TREAT_DECIMAL_AS_INT=false in the server properties.

---

Best Regards,

Icey

[Anonymous]

Hello Icey,

Thanks for the workaround. Any update on the status of the fix?

[pankajbirle]

I have used the below option and it would work perfectly. 

"If ARROW, run ALTER USER SET ODBC_QUERY_RESULT_FORMAT='JSON'"

Thanks for your support.

Regards
Pankaj Birle

[Anonymous]

Hello Icey 

We have implemented option 1 but this did not fix the problem for us. Do you know if we need to implement both fixes ?

Regards 

Murtuza

[Icey]

Hi @Anonymous ,

Please check if the option 2 could work for you. If not, please let me know.

Best Regards,

Icey

[vidhiarora095]

After running the statements on snowflake my reports are now refreshing as well. Is this a long-term solution as you suggested this was a workaround?

[ManuelaOsorio]

I had the same problem since Tuesday, November 16. Yesterday I implemented this and the reports are now working. My question is, @Icey , do you know why this happened? I'm looking and I can't find an explanation of what happened and why this change you suggested makes the reports work

[Icey · Accepted solution]

Hi @vidhiarora095 and @ManuelaOsorio ,

There is an issue in Snowflake driver that when combined with some options causes it to throw exceptions and end up in an infinite loop.

Engineers are working on the fix, ETA is 12/31/2021. Please be patient to wait for fixing. If there is any news, I will update it here.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pwalter]

Hello, it is now 4/14/2022 and this is still happening for dataflows the workarounds do not fix anything. 

Is there a status on this fix?

[bgarden]

Hi @Icey,

This issue was seemingly fixed for a while, but we have now experienced the same issue again for a few of our dataflows for the past week.  Has there been an update to fix this?

Full Error:

Error: Data Source

Error : DataSource.Error: ODBC: ERROR [22007] [Microsoft][Support] (40490) Invalid date time or timestamp value.

DataSourceKind = Snowflake

DataSourcePath = [PATH].snowflakecomputing.com;[WAREHOUSE]

OdbcErrors = #table({"SQLState" "NativeError" "Message"} {}).

RootActivityId = f6f77b7e-1145-42a4-a736-fcca3186b22b.

Param1 = DataSource.Error: ODBC: ERROR [22007] [Microsoft][Support] (40490) Invalid date time or timestamp value.

DataSourceKind = Snowflake

DataSourcePath = [PATH].snowflakecomputing.com;[WAREHOUSE

OdbcErrors = #table({"SQLState" "NativeError" "Message"} {}) Request ID: 13e5a2da-c662-a2f6-b5cf-766c8a17b06b.

[robemie_0119]

Hi! Is there an update on this? All my reports are having this issue 😞

[pankajbirle]

I am facing the same issue last day. I have Snowflake as database. Could you please anyone helpout me?

Thanks in advance.

[Anonymous]

We can also seeing the same connectivity problem with snowflake we have been refreshing our datasets consistently for the last 2 months and this is the first time our datasets have failed. 

The problem is exactly the same the dataset refresh from Snowflake runs for a lot longer than the existing daily refresh and then fails with this error. ODBC: ERROR [22007] [Microsoft][Support] (40490) Invalid date, time or timestamp value

[cfields22]

I just received the same error message and am also retrieving the data from snowflake.  My report ran at noon PST and seemed to be near completion (ran for 23 minutes) before the scheduled refresh failed. 

Not sure if there was a momentary error with Snowflake, but I was able to refresh the data just fine from the desktop app.  I'm going to schedule a request for 1:30pm PST to see if the issue repeats.