The wait operation timed out Win32Exception (0x80004005): The wait operation timed out azure

Question

Following error comes when retrieving large amount of data from sql azure. I have already implement the Transient Fault Handling but still getting this error

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ComponentModel.Win32Exception: The wait operation timed out

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[Win32Exception (0x80004005): The wait operation timed out]

[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +1789270 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +5340622 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +244 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1691 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +275 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +1421 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +177 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +208 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +163 System.Web.SessionState.SqlSessionStateStore.SqlExecuteNonQueryWithRetry(SqlCommand cmd, Boolean ignoreInsertPKException, String id) +98

[HttpException (0x80004005): Unable to connect to SQL Server session database.] System.Web.SessionState.SqlSessionStateStore.ThrowSqlConnectionException(SqlConnection conn, Exception e) +235 System.Web.SessionState.SqlSessionStateStore.SqlExecuteNonQueryWithRetry(SqlCommand cmd, Boolean ignoreInsertPKException, String id) +390 System.Web.SessionState.SqlSessionStateStore.SetAndReleaseItemExclusive(HttpContext context, String id, SessionStateStoreData item, Object lockId, Boolean newItem) +589 System.Web.SessionState.SessionStateModule.OnReleaseState(Object source, EventArgs eventArgs) +565 System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +136 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +69

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.34009

https://i.stack.imgur.com/8BloW.png

Answer 1

The SqlClient.SqlCommand object has a property CommandTimeout. It's default value is 30 (seconds). You should set it to a higher value.

Answer 2

You can set the timeout value higher or you can turn off the timeout value, as I did in the below code. Note that you may encounter errors if the object that holds the data exceeds 2Gb. You may want to consider redesigning your query to take smaller chunks of data at a time.

// I'm populating an ADO.Net DataTable for this demo but populate whatever object you'd like
DataTable DtFromSQL = new DataTable();
SqlConnection myConnection = new SqlConnection("ConnectionString");
myConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand("enter some SQL query here", myConnection);
// A CommandTimeout Value of 0 turns the timout off, otherwise you can set it to some value in seconds
myCommand.CommandTimeout = 0;  
myReader = myCommand.ExecuteReader();
DtFromSQL.Load(myReader8);
myConnection.Close();
DtFromSQL;