PowerBi report refresh timing out
We have a report that will always refresh successfully when refreshed from the desktop. However, when we have the refresh scheduled, it is constantly failing, not 100% of the time but maybe 90% of the time. Below is a sample of the log showing the failure. It seems to indicated a timeout error but it's not based on any server or report configurations, as those are at 15 minutes. In addition, as you can see, the timeout is happening within a minute of the report refresh launching. When I have tracked what is happening in the database, I see the report initiating connections, running the required queries and getting the results back in a timely manner. I opened a ticket with msft but they have not been of any assistance and I am at the end of my rope. A couple of questions. One is, is the timeout happening in the PowerbiReportServer database, since it always works when the refresh is done from the desktop, bypassing this database? I have tried to look there in the catalog table and SQL logs, but have never found anything of help. I have combed the forums as well, and don't see any solution for this. It is on premise and the SQL server where the PowerbiReportServer database is the same that hosts the database being queried. Any help would be deeply appreciated. EventType: DataModelRefresh 2021-02-09 11:18:07.8314|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Data Refresh. 2021-02-09 11:18:07.9877|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Retrieving PBIX AsDatabaseInfo. 2021-02-09 11:18:08.2846|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Verifying all the data sources are embedded. 2021-02-09 11:18:08.5814|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Verifying connection strings are valid. 2021-02-09 11:18:08.8002|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Streaming model to Analysis Server. 2021-02-09 11:18:09.5033|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Refreshing the model. 2021-02-09 11:18:19.3317|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Removing credentials from the model. 2021-02-09 11:18:19.9567|INFO|40|Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Starting Saving model to the catalog. 2021-02-09 11:18:43.0369|ERROR|40|Error Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Error Failing saving model to the catalog, Exception System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest) at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest) at System.Data.SqlClient.SqlInternalTransaction.Commit() at System.Data.SqlClient.SqlTransaction.Commit() at Microsoft.ReportingServices.CatalogAccess.Streams.ExtendedContentWritableStream.Flush() at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.AnalysisServicesDataRefresh.SaveDatabaseToCatalog(String databaseName, Guid catalogItemId) at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<>c__DisplayClass38_0`2.<ExecuteStepWithExecLog>b__0() at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<>c__DisplayClass34_0.<ExecuteActionWithLogging>b__0() at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<ExecuteFuncWithLogging>d__33`1.MoveNext() ClientConnectionId:66ee1b33-29f4-4cef-8fd8-b58886b32b29 Error Number:-2,State:0,Class:11 2021-02-09 11:18:43.2088|ERROR|40|Error Processing Data Model Refresh: SessionId: d1571892-238c-4c7e-928d-f1312d454c4e, Status: Error Failed Data Refresh, Exception System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.ExecuteActionWithLogging(Action methodToExecute, String description, String localizedDescription, String messageInFailure, RefreshInfo refreshInfo, DataAccessors dataAccessors, ReportEventType operation, Boolean isDataRetrieval, Boolean showInExecutionLog, Boolean sendToTelemetry) at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.AnalysisServicesDataRefresh.RefreshData(RefreshInfo refreshInfo) at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<>c__DisplayClass34_0.<ExecuteActionWithLogging>b__0() at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<ExecuteFuncWithLogging>d__33`1.MoveNext()
Server refresh gives Table not found error but fine in desktop
I'm trying to set up my scheduled refresh for my PowerBI report - the connection is an ODBC on import, I have the required drivers and get a green check when I test the connection, however when I refresh I get the following error: SessionID: b5ba2f83-45cb-4fab-8a0e-8a79b6cdbf0c [0] -1055784932: DataSource.Error: ODBC: ERROR [42S02] [Cache ODBC][State : S0002][Native Code 30] [D:\Program Files\Microsoft Power BI Report Server\PBIRS\ASEngine\MDataEngine\Mi] [SQLCODE: <-30>:<Table or view not found>] [Location: <Prepare>] [%msg: < Table 'DBO.DSKILLSETSTAT' not found>]. DataSourceKind = Odbc. DataSourcePath = dsn=MYDSNLOCATION. OdbcErrors = #table({"SQLState", "NativeError", "Message"}, {}). . The exception was raised by the IDbCommand interface. [1] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface. [2] -1055129594: The current operation was cancelled because another operation in the transaction failed. [3] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface. [4] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface. My refresh works just fine in PowerBI desktop, no issues with queries on any tables, it's clearly connecting to the source ok, I'm not sure what I'm missing. I can refresh and save the updated file in desktop and the report data refreshes on server. I'm not sure how or why it can't find the table any more. Here's a snip of the connection structure if I'm creating a new source - the table being queried is called dSkillsetStat in the dbo folder
