07-12-2018 12:34 AM
Given the pain that I have been through over the past couple of weeks, I figured I would document the a description of my failure as well as the solution I put in place to address the issue:
For several weeks, my large ODBC queries against an AS400 data warehouse (IBM - i Series) worked well. Then all of a sudden they failed to updated and
- HYT00 - Login Timeout Expired
- In my example, I have 9 different Queries that timeout after trying to shake hands and authenticate with the server
- In a cascading manner, all the 9 of the queries fail after the HYT00 code is triggered
- CWBCO1051 - User Specified Timeout
- Similar to the the ODBC documented above, all queries fail after the code is triggered
- ODBC Power BI Failure
Solution Option 1:
- Add timeout parameter to connection string
;Connect Timeout=xx (xx = seconds)
- The connection string update can be used for both ODBC & OLE DB configurations
Solution Option 2:
- Add timeout parameter to Query argument within M Code / Advanced Editor
- [Query="SELECT ...", CommandTimeout =#duration(0,1,0,0)]
I prefer Option 1, because the user will lose the ability to edit the Source Query within the UI as shown in Option 1. All updates to the source query will have to made through the Advanced editor after that point, which can be rather cumbersome with complex statements.
07-13-2018 07:23 PM
Thanks for the sharing.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.