Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

Question

so I have this function

Public Function getDataSetonDataTable(ByVal query As String, ByVal    sDatabaseConnection As String) As DataTable
    Dim dsRecordset As New DataSet
    Dim dtReturn As DataTable

    Try
        open(sDatabaseConnection)
        oConn.Open()
    Catch ex As Exception
        log.infoError(ex, "GET DATA SETON DATA PART 1 ON cDatabase.vb")
        Return Nothing
    End Try

    Try
        createLog("Q || " & query)

        dsRecordset = oDatabase.ExecuteDataSet(System.Data.CommandType.Text, query)
        dtReturn = dsRecordset.Tables(0)
    Catch ex As Exception
        If ex.Message.ToString = "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding." Then
            Try
                createLog("Query : " & query)
                dsRecordset = oDatabase.ExecuteDataSet(System.Data.CommandType.Text, query)
                dtReturn = dsRecordset.Tables(0)
            Catch ex2 As Exception
                log.infoError(ex, "GET DATA SETON DATA PART 2 ON cDatabase.vb")
                dtReturn = Nothing
                ErrorMessage = ex2.Message.ToString
                MessageBox.Show(ErrorMessage, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
            Finally
                oConn.Close()
            End Try
            Return dtReturn
        End If
        dtReturn = Nothing
        ErrorMessage = ex.Message.ToString
        MessageBox.Show(ErrorMessage, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
    Finally
        oConn.Close()
    End Try
    Return dtReturn
End Function

this is the data-retrieving codeline

dsRecordset = oDatabase.ExecuteDataSet(System.Data.CommandType.Text, query)

however, if it executes a complicated query, it goes to this exception after exactly 60 seconds

System.Data.SqlClient.SqlException was caught
  Class=11
  ErrorCode=-2146232060
  LineNumber=0
  Message="Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."
  Number=-2
  Procedure=""
  Server="KF-DC1DB01"
  Source=".Net SqlClient Data Provider"
  State=0
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       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 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String tableName)
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet(DbCommand command)
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet(CommandType commandType, String commandText)
       at Database.cDatabase.getDataSetonDataTable(String query, String sDatabaseConnection) in D:\My Project\ClassLibraryKalbe\Database\cDatabase.vb:line 117

how to set the timeout to a longer period?
I use vb.net in Visual Studio 2005
Thanks in advance.

score 0 · Accepted Answer · edited May 23 '17 at 10:30

0

inspired by the answer from Sql CommandTimeout set for EnterpriseLibrary, I assigned the query into a System.Data.Common.DbCommand variable, assigned a timeout, and called ExecuteDataSet() using the dbCommand variable.

edited May 23 '17 at 10:30

Community

1
1

answered Dec 06 '16 at 11:23

budisanto

1
4

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

1 Answers1