ODBC & OLE DB Timeout Errors

[natex8]

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
• ODBC 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
• OLE-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
• 

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.

[v-chuncz-msft]

@natex8,

Thanks for the sharing.