I have made a simple ASP.Net program which would pass parameters to the Stored Procedure in SQL Sever and would return back a row or two. The code is working fine when i try it on test DB which size about 3MB how ever if the code is tested on bigger size DB of about 2GB it will give this error:

Error

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. 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.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Things I have checked:

1) I have tried putting the code in Try Block

Try

dbConnection.Open()

DataReader1 = dbCommand.ExecuteReader()

Herd_List.DataSource = DataReader1

Herd_List.DataBind()

DataReader1.Close()

dbCommand.Dispose()

Finally

dbConnection.Close()

End Try
dbConnection.Dispose()

2) Have made sure that connections are closed explicitly

Anyone kind enough to bring me out of this miserable condition ?

thanks in advance,

Umer

I believe you need to change the timeout parameters in SQL Server.

I forgotten to mention..the query takes 0 Secs in query analyser taking same parameters in where clause to display the result.
Increasing timeout property even to 1000 wouldn't help and in my opinion timeout parameters shouldn't be changed as it will make user wait for more so this aint a good approach.

0 seconds in Query Analyzer against the 3mb or 2GB database.

If it is a matter of the amount of data being pulled back, restructure your query. Also, rethink the reson for pulling so much data. Usually, it isn't necessary to pull so much back at one time.

Can I know how to change the parameters in SQL server 2000,Becuase I also got the same problem in my Vb.net application error log[u].(Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding)</u>.

Any other good solution beside change the timeout parameters in SQL server.
thanks

Anybody can reply me ASAP, Im in trouble for the banking system.:(