WAITFOR Receive

Question

Hi all

I have this code below.

The select statement is called by Asp .Net application (class) via Storeprocedure.

I am currently getting Timeout expired on a daily basis.

I read some article. and comes to the conclusion that Waitfor method could help prevent the errors.

Atleast the Web Apps could wait a little before sending out this error:

If I use WAITFOR (RECEIVE...) so that the sp could wait for 10 seconds at least until a record is returned.

could this help prevent the error?

Can I find some example or tutorial?

"Details: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. "

select	Person.Name,
		Person.Age
		Adress.ID
		FROM		Person
		inner join	Adress on (Person.ID = Adress.ID) 

Thanks in Advance

---

AKE

Answer 1

If you specify WAITFOR...RECEIVE without the T-SQL timeout option, your application will wait until messages are available.  That may be forever, and forever is a long time to wait :-)

So if you don't want to wait forever, specify the timeout option.  Here's an example from the Books Online (http://msdn.microsoft.com/en-us/library/ms186963.aspx) to wait up to 60 seconds:

WAITFOR (
      RECEIVE *
      FROM ExpenseQueue ),
  TIMEOUT 60000 ;

Be aware that you will need to set the CommandTimeout property of your SqlCommand to either zero (infinite wait) or to a value larger than the T-SQL timeout.  The default is 30 seconds so you'll otherwise get a timeout after 30 seconds regardless of the T-SQL specification.

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 2

And make sure you understand that WAITFOR ... RECIEVE is only for Service Broker Queues.  It can't be used with ordinary tables.

The error you are receiving is not coming from SQL Server.  This is a client-side timeout controlled by the setting of SqlCommand.CommantTimeout.

David

---

David http://blogs.msdn.com/b/dbrowne/