By Marcia G Akins - 6/14/2011
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
|
By Ivan George Borges - 6/14/2011
Hi Marcia.
See if this thread helps you:
http://forum.strataframe.net/FindPost29234.aspx
|
By Peter Jones - 6/17/2011
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
|
|