Assistance Required: SQL Timeout Issue When Connecting Power BI

[Pavan_123456789]

I’m encountering an issue while attempting to connect Power BI to our SQL Server database. The error message reads:

“Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.”

I would appreciate your guidance on whether this issue might be related to SQL Server settings or if there’s something within Power BI that I should adjust.

Here are a few details for context:

1. I'm pulling data from [ stored procedures]
2. I’ve tried re-running the connection, but the timeout persists.

If this issue is on the SQL Server side, could you advise on which settings might need adjustment?

[Mujahid_Syed]

Hi @Pavan_123456789 - Since you already loaded one month data, and you are facing issue while loading the full dataset. Below are the options you can review:

1) 80% of the times, issue will be at SQL Server end because of poor indexes, stored procedure logic, and if any blocks. Try running sp_who2 in SSMS to check if any blocks, review stored procedure logic, review indexes and query performances by using execution plans.

2) Review timeout settings in Power BI connection to SQL Server - Every time a Power BI Import mode dataset connects to a data source it goes through a Power Query query, and inside the code of that Power Query "Advanced Editor" will be an M function that connects to a specific type of data source. Most – but not all – of these M functions have the option to set timeouts. The M functions that connect to relational databases (for example Sql.Database which is used to connect to SQL Server-related sources, or Odbc.DataSource which is used to connect to ODBC sources) typically allow you to configure two types of timeout:

A connection timeout, which specifies how long to wait when trying to open a connection to the data source
A command timeout, which specifies how long the query to get data from the source is allowed to run

[nandic]

@Pavan_123456789 in that case, the issue is for sure stored procedure that has complicated SQL logic which is slow for execution.
What i would do firstly: if you have access to SQL Management Studio, login and test this stored procedure there. 
If you don't have access then contact database developer to check it on his/her side.
If it works slowly also there, the issue is stored procedure.

If it works fine and fast in SQL Management Studio, then we can check what might be the issue in Power BI.
Is it the only table in Power BI data model or maybe it has relationship to other complex tables?
Did you try to publish this dataset to Power BI Service and then to refresh it there? Does it return the same error?

Cheers,
Nemanja

[nandic]

@Pavan_123456789 ,
1) does connection to database work? Once you enter server name, credentials, do you get to a step to choose tables/views/st procedures? If not, something is wrong with server name, credentials or you need to be whitelisted on database level
2) if came to next step, you can select tables/views/st procedures. However, when you try to retrieve data from database, it returns error. Check with database developer who built stored procedure if it works fine on database, maybe you need some additional parameters.

But it seems that this is related to database connection.

Cheers,
Nemanja

[Pavan_123456789]

Hi @nandic ,

Thank you for your response.

I was able to load data successfully for a single month, but when I attempt to load the complete dataset, I encounter the error previously mentioned. Could you please advise on potential solutions or adjustments I should consider to resolve this issue?

Thank you for your guidance.

[Omid_Motamedise]

See the below blog related to the error you are faced

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/timeout-expired-error