Summary:
This article troubleshoots an error that occurs when running a report when SQL is being heavily used.
Symptoms:
When a user opens any file within Report Designer and attempts to run the report,"Please wait, loading…" displays for an extensive amount of time before they receive the following error message:
There was a problem loading the System Integration Package for this Report.
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.SqlInternalConnection.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.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at OneStop.Toolkit.Data.SchemaHelper.
at OneStop.Toolkit.Caching.CacheExtensions.Get[T](ICache cache, String key, Func`1 factory, TimeSpan slidingExpiration, DateTime absoluteExpiration, Boolean removable)
at OneStop.Toolkit.Caching.CacheExtensions.Get[T](ICache cache, String key, Func`1 factory, TimeSpan slidingExpiration, Boolean removable)
at OneStop.Toolkit.Data.SchemaHelper.GetColumnInfo(String schema, String table)
at OneStop.Meta.MetaDimension.SetDynamicDataType(DatabaseConnection conn)
at OneStop.Meta.DynamicModelHelper.GetAttributeProperties(MetaDimension dim, DatabaseConnection conn, IEnumerable`1 rows)
at OneStop.Meta.DynamicModelHelper.
at System.Collections.Generic.List`1.ForEach(Action`1 action)
at OneStop.Meta.DynamicModelHelper.GetAttributeProperties(MetaModel model, DatabaseConnection conn)
at OneStop.Framework.Engine.Integration.IntegrationAdapter.CreateCompanyModel(IntegrationModel intModel, Boolean removeInvalid)
at OneStop.Framework.Engine.Integration.IntegrationAdapter.BuildCompanyModel()
at OneStop.Framework.Engine.Integration.IntegrationManager.
at OneStop.Framework.Engine.Integration.IntegrationManager.GetRuntimeModel(SourceEnvironment sEnv)
at OneStop.Framework.Engine.SourceEnvironment.PrepareRuntimeIntegration(MetaModelContext modelContext)
at OneStop.Framework.Engine.SourceEnvironment.PrepareRuntimeIntegration()
at OneStop.Framework.Engine.SourceEnvironment.EnsureModelContext()
at OneStop.Facade.Meta.GetMetaInfo(String reportId, String sourceContext, String userName, String dictionaryName, MetaInfoTypes types)
at OneStop.StandaloneAdapter.ReportDesign.GetMetaInfo(String reportId, String sourceContext, String dictionaryName, MetaInfoTypes types)
at OneStop.ReportDesigner.DesignEnvironment.RefreshMeta(MetaInfo oldInfo, String dataSource, MetaInfoTypes types)
at OneStop.ReportDesigner.DesignEnvironment.RefreshMeta(String dataSource, MetaInfoTypes types)
at OneStop.ReportDesigner.DesignEnvironment.RefreshMeta()
at OneStop.ReportDesigner.Forms.ReportPane.
at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
ClientConnectionId:d45f51d0-81ea-4077-9ebb-5a3a983b9313
Error Number:-2,State:0,Class:11
OSR Version: 4.6.40512.1445
Causes:
The timeout error is due to SQL is being heavily used at that moment so SQL is slow to query and times out.
Resolution:
Have SQL DBA look into SQL bandwidth when the timeout error occurs to see if memory and CPU spikes up. Also, open task manager on local computer and verify the CPU and memory when running the report when it errors out.
Properties:
BI360 Reporting 4X