cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
natex8 Frequent Visitor
Frequent Visitor

ODBC & OLE DB Timeout Errors

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 

ODBC Failure:

  • 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
  • image.pngODBC Power BI Failure

OLE-DB Failure:

  • CWBCO1051 - User Specified Timeout
  • Similar to the the ODBC documented above, all queries fail after the code is triggered
  • ODBC Power BI Failure
  • image.pngOLE-DB 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
  • image.png

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.

1 REPLY 1
Highlighted
Community Support Team
Community Support Team

Re: ODBC & OLE DB Timeout Errors

@natex8,

 

Thanks for the sharing.

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