Hi All.

I have a query that takes a couple of minutes to execute in SQL Server. When I try to execute it in the app, I get this error:

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

Source     : .Net SqlClient Data Provider

Stack Trace:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.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.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)
   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(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at SleepSuccess.Libs.DownloadMailMerge.CreateMailMergeFile()
   at SleepSuccess.Forms.HCSmain.downloadMailMergeMenuItem_Click(Object sender, EventArgs e)
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ToolStrip.WndProc(Message& m)
   at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativewindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m)
   at System.Windows.Forms.Nativewindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

So how do I lenghten the timeout period to make the error go away?

TIA.

MArcia


 

Hi Marcia.

See if this thread helps you:

http://forum.strataframe.net/FindPost29234.aspx

Hi Marci,

All our reports are selectable from a reports menu which is populated from a couple of tables. With each report definition we have a column called MEICmdTimeout into which we can enter a value if the report can be long running. When we create the data table for the report we do the following:

 

        'Make a copy of the Parameters to the command that won't be cleared
        'when the Parameters in the command are cleared
        Dim Params As SqlParameter() = Sundry.CloneParams(ProcParam)

        'Fill the reporting BO with data
        Dim cmd As New System.Data.SqlClient.SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = bo.UpdateStoredProcedureName.ToString
        If MEICmdTimeout > 0 Then
            cmd.CommandTimeout = MEICmdTimeout <<<<<< THIS IS WHERE WE SET A SPECIFIC TIME OUT
        End If
        If Not Params Is Nothing AndAlso Params.Length >= 1 Then
            cmd.Parameters.AddRange(Params)
        End If

        bo.FillDataTable(cmd)
        cmd.Dispose()

        Return bo.CurrentDataTable

Cheers, Peter