External Activation and Receive Timeout

I have a set of service broker services setup that rely on external activation to process messages. I'm using the GotDotNet ExternalActivator, and it launches console applications that do the actual retrieval from the queues. The console applications are written to run continuously to avoid the cost of starting up .NET based console apps over and over again.

I am observing very odd timing behavior. With the receive queues empty and the external activator configured to run a minimum and maximum of 5 instances, I observe in SQL Profiler that most of the receive operations finish in about the same amount of time as my WAITFOR command in my receive stored procedure. However, there is usually one receive command that consistently takes upwards of 30 seconds and often causes sql timeout exceptions to be thrown. I know that I could code around this, but I wasn't really expecting this behavior.

Does anyone have any thoughts on why it might be occurring? I would have expected to routinely see my receive operations taking 15 seconds, give or take, especially when the queue is empty. Also, I have observed this behaviour on both SQL 2k5 Express and Dev Editions, so I don't think it's a version thing.

The stored procedure I am using to do the receive is:

Create PROCEDURE [dbo].[P_RTD_MessageBase_Receive]

@.receiving_queue_name varchar(255),

@.receiving_time_out int

AS

Declare @.receiveQuery nvarchar(300)

Set @.receiveQuery = 'WAITFOR(RECEIVE * FROM [dbo].['+ @.receiving_queue_name +']),

TIMEOUT ' + cast (@.receiving_time_out as varchar)

Execute sp_executeSql @.receiveQuery

Using SQL profiler can you find out if the actual command being executed has the correct timeout?

Some questions to help me understand why you made some design decisions:

1. Why do the apps run forever instead of shutting down after a long period of inactivity? Sure I understand you don't want yout .NET programs being launched and torn down over and over but you could use a longer timeout to avoid this. (Also, you should probably ngen your assemblies to improve startup time).

2. Why are you setting minimum instances to 5? Most people want minimum to be 0 so that there are no instances running when the queue is empty. Do all instances execute the same code path or are you doing something special here?

|||

I have confirmed that the actual command that seems to take much longer than the timeout has the correct timeout. IOW, I issue a receive with a 15000 ms timeout, and it takes about 28000 ms even though the queue is completely empty. The other four receives seems to finish and around 15000 ms consistently.

I don't have a great reason for the apps to run forever, except to avoid startup time. I could implement a longer timeout, but I am seeking to minimize the amount of time it takes to process a single message. The nature of the system is that messages drip in during some periods of the day, and I don't really want those messages to have to wait any longer than when messages are regularly coming in. nGen could help, but I still think my approach is the fastest.

I was setting the minimum instances to 5 in order to test the apps to make sure they weren't leaking any memory and that they did indeed run indefinitely.

How might these three issues affect the timeout?

|||

Is it possible for you to capture the trace using SQL Profiler with all Broker events as well as the following events enabled?

- RPC:Starting
- RPC:Completed
- SP:StmtStarting
- SP:StmtCompleted
- SP:Starting
- SP:Completed
- SQL:StmtStarting
- SQL:StmtCompleted
- SQL:BatchStarting
- SQL:BatchCompleted

You could e-mail that to rushi (dot) desai (at) microsoft (dot) com.

My questions were unrelated to the problem you are facing. I was intrigued by the atypical setup and so I was curious why you were using this setup.

Thanks,

Rushi

|||Hey Rushi - I just sent the trace file to you...|||

Rushi has come back to me and said that this is a bug. I have posted a bug report at:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=234866

Extended Stored Procedure

When I run the below (sp_OACreate) in SQL Query Analyzer it runs fine. When
I add the same to a stored proc my app hangs and I get a timeout.
Any help is greatly appreciated. I really don't want to create a job that
runs this.
EXEC @.hr = sp_OACreate 'MyLocalEXE.Host', @.object OUT, 4
IF @.hr <> 0 BEGIN EXEC sp_raiseoaerror @.object, @.hr RETURN (-1) ENDHi
At a guess as the application hangs it probably means it can't find the
object, which implies it is not being run in the same context and/or possibl
y
a lack of permissions. You also need to be symin to run sp_OACreate.
John
"Mark - HYPERe" wrote:

> When I run the below (sp_OACreate) in SQL Query Analyzer it runs fine. Wh
en
> I add the same to a stored proc my app hangs and I get a timeout.
> Any help is greatly appreciated. I really don't want to create a job that
> runs this.
> EXEC @.hr = sp_OACreate 'MyLocalEXE.Host', @.object OUT, 4
> IF @.hr <> 0 BEGIN EXEC sp_raiseoaerror @.object, @.hr RETURN (-1) END

extended connect timeout does not work

I have a stored proc to query a very large database, and it always times out at the 30th sec, even though I have changed the connect timeout to 300, as follows

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

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\Administrator\My Documents\abc.mdf";Integrated Security=True;Connect Timeout=300;User Instance=True

This timeout issue does not occur to smaller databases. Any idea?

hi,

the connection timeout setting is relevant to "connection timeout"

you have to modify the command timeout property within your code so that the executing command enables more time..

tipically

Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand With cmd .CommandText = "the command text or procedure name" .CommandType = CommandType.Text ' or CommandType.StoredProcedure .CommandTimeout = 300 'or whatever .Connection = yourConnectionObject End With

regards

|||Thanks for pointing out this. Can this be done once for all / globally, say by using a SQL statement?|||

hi,

AFAIK, that should be done for each command..

regards

|||I wish I am not asking too much, but how can this be done in a stored procedure, please?|||

hi,

actually that should be done outside the procedure, in the command (or whatever you use) to execute the actual procedure..

there's a server wide option, query wait, but I'd not set it to a defined/manual value as this one will be valid for all queries at instance level (again, server wide option).. I'd go for the standard behavior only setting "manual configurations" for particular activities via the (external) command property..

regards

Exporting to Excel; timeout

Exporting to Excel; timeout

I have 8000 rows in the report and trying to export excel in asp.net code, it does not export in the Report manager and it give exception saying “The underlying connection was closed: An unexpected error occurred on a receive.”

Small number of rows are exported correctly. Is there any setting I can change sin RS2005 web service

Follow this KB http://support.microsoft.com/default.aspx/kb/909678

Exporting the Report into Excel : Timeout Problem

Hi,

I have installed the SSRS 2005 beta version, when I am exporting the report into excel format it is working if my report has 1 to 3 pages. But the same report contains more than 10 pages then I will be getting the TimeOut error.

The operation has timed out

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.Net.WebException: The operation has timed out

Any suggestions are welcome.

Thanks in Advance

Hello,

Can you export to other formats (HTML, PDF) without problems? How much information do you actually have on a page ? How many columns and rows in your excel spreadsheet?

Thank you,

Nico

|||

Hi,

I made the same experience, when exporting to XLS I got a timeout error message. This also happens when exporting to pdf, but not when exporting to csv, html. This only happens with larger reports (In our case approx. 700 pages). We would need some help here, too.

Thx

Michael

Exporting the Report into Excel : Timeout Problem

Hi,

I have installed the SSRS 2005 beta version, when I am exporting the report into excel format it is working if my report has 1 to 3 pages. But the same report contains more than 10 pages then I will be getting the TimeOut error.

The operation has timed out

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.Net.WebException: The operation has timed out

Any suggestions are welcome.

Thanks in Advance

Hello,

Can you export to other formats (HTML, PDF) without problems? How much information do you actually have on a page ? How many columns and rows in your excel spreadsheet?

Thank you,

Nico

|||

Hi,

I made the same experience, when exporting to XLS I got a timeout error message. This also happens when exporting to pdf, but not when exporting to csv, html. This only happens with larger reports (In our case approx. 700 pages). We would need some help here, too.

Thx

Michael

|||Is this answered already?I almost have te same problem.I am using a custom code.When i am exporting my report to PDF, the operation time out.But why not when exporting to Excel or HTML.