CODEWITHSUNDEEP
vb.netwinformsstored-procedurestimeout
Udhay
Udhay

Reputation: 71

Time Out Expired Error while executing a stored procedure that retrieves 7000+ datas

I have written a stored procedure which retrieves more than 7000 rows. While executing the stored procedure in my VB.NET winforms application, I'm getting an error like as follows.

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

I googled and tried like using these lines of codes which wasn't useful. 

    sqlCmd.CommandTimeout = 0
    sqlCmd = New SqlCommand("waitfor delay '00:00:60'")

Please help me with this.

Upvotes: 0

Views: 6212

Answers (3)

E-r Gabriel Doronila
E-r Gabriel Doronila

Reputation: 533

It is not advisable to set your timeout to 0. The remaining answer would be only to increase the limit BUT..

Increasing the timeout is like a band-aid solution. What if you have a longer query? Will you just increase the timeout again?

To solve this, please refer to my answer here..

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

Upvotes: 0

brendan
brendan

Reputation: 29976

There are multiple places you can be experiencing a timeout. Some things to try.

  1. Run the stored procedure with the exact same parameters in Sql Management Studio. How many seconds does it take? This will give you an idea of how long you need your timeout to be. If it takes more than 30-60 seconds, consider revising your sql, added indexes and otherwise tune your database.
  2. CommandTimeout - This is the timeout for your particular command. You should avoid setting it to 0, although this may work, http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx
  3. ConnectionTimeout - This is the timeout property for your entire connection. If this is less then your CommandTimeout it will override. Again, 0 should be avoided here. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx

Upvotes: 1

Ghyath Serhal
Ghyath Serhal

Reputation: 7632

You should increase the commandTimeOut and not put it 0.

Try to put the value 60, which means 1 minute.

Upvotes: 0

Related Questions