TimeOut Exception

[pa1asp7]

Hi All

I am facing SQL Server Timeout exception.

try
{

SetStoredProcCommand(SPRetrieveIntSchedReport, DatabaseConfigKey);

AddInParameter("@FromDate", DbType.DateTime, schedule.FromDate);

AddInParameter("@ToDate", DbType.DateTime, schedule.ToDate);

AddInParameter("@ApplnType", DbType.String, schedule.ApplnType);

IDataReader dataReader = ExecuteReader();

while (dataReader.Read())
{ .....}
}
When the control reaches the data reader its taking much time to enter the WHILE loop, and some times its getting in and its done fine,but sme times facing this SQL SERVER TIMEOUT EXCEPTION.

please help.

Thanks in advance.

 

[Fork501]

Have you tried putting the results in an adapter and parsing it after the data set is closed?

For example:

protected void SQLFunction()
{
	DataTable dt = new DataTable();
	using (SqlConnection myConn = new SqlConnection(strConnect))
	{
		string strSQL = "SELECT TOP 10000 * FROM [TableName]";
		myConn.Open();
		SqlCommand myCommand = new SqlCommand(strSQL, myConn);
		SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
		myAdapter.Fill(dt);
		myConn.Close();
	}
	foreach (DataRow row in dt.Rows)
	{
		lblResults.Text += row[0].ToString() + "<br />";
	}
}

With those code, I took 10,000 results and was able to display everything from the first column without a timeout.

Of course, youd need to adapt this to use the Stored Procedure that you want.

In my database, I have 31 columns in the particular table I tried it on and this entire code executed in roughly 12 seconds.

I hope this helps!

~Derek