How to fix this error in Microsoft SQL: SQL job is failing. OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Query timeout expired...

Question

Hello,

Kindly advise how to solve below issue:

we have one job that's failing is Microsoft SQL.

This issue is affecting the Power BI reports as well.

Below error is found:

SQL job is failing. OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Query timeout expired at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult) at Microsoft.AnalysisServices.Xmla.XmlaClient.ExecuteStatement(String statement, String& result, Boolean skipResult, String properties, String parameters) at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.ExecuteStatement(String stmt, StatementType stmtType, Boolean withResults, String properties, String parameters, Boolean restrictionListElement, String discoverType, String catalog) at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.SendQuery(String query, Boolean withResults, String properties, String parameters, String catalog) at OlapEvent(SCH_STEP* pStep, SUBSYSTEM* pSubSystem, SUBSYSTEMPARAMS* pSubSystemParams, Boolean fQueryFlag). The step failed.

Answer 1

Hi @Aya,

Thanks for your information.

As Erland Sommarskog says, the current information is not enough for us to determine the issue, could you please provide more detailed messages to help us narrow down the issue?

In addition, according to the information you provided, I can make the following suggestions for you:

• Right-click the server name in Management Studio > Properties. Select the Show Advanced (All) Properties check box. Then adjust the ExternalCommandTimeout setting.
• Tune the relational database system.

This official document explains the causes and steps for this issue in detail, hope this can help you well.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

 

Answer 2

The first step of solving a problem is to perform troubleshooting. This includes identifying what the job is doing, to what is connecting to etc. If the job is connecting to SQL Server, it is a good idea to find out what query it was trying to run.

I don't know if you have performed any of that, but if you did it, you would help yourself with sharing that information. As it stands right now, I only see Analysis Services, so I am not sure what sort of job you are running. You have labelled your question SQL Server and Microsoft Graph, two quite different things, so it is not easy to tell.

In case, the above code is trying to run a query SQL Server, there are three possible remedies:

1. There is a blocking issue. That is, another process is holding a lock which blocks this query. This is typically transient, and if you try again later, things may work.
2. There is a slow query that needs tuning or an index.
3. If you can accept the job running for a longer time, increase the execution time out.