Re: C# command doesn't time out appropriately...why?

No, not a dumb question at all. I have executed this same query in a
different application...a Visual Basic application using ADO. The
specific parameters that we provided to the stored procedure are known
to take about 80 seconds to successfully complete. There is only one
record in our database that matches the search criteria, but it takes
about 80 seconds to successfully find the record. Our VB application
has a timeout set to 60 seconds, so that's how the problem originally
came to our attention. We knew that the matching record existed, but
only by increasing the timeout in our VB application, little by little,
to 80 seconds did the stored procedure call execute successfully.
We've spoken with our database administrator and they're going to do a
database reorg soon that will hopefully solve the problem.

So...we're trying to duplicate the same search in our web service
application, and since we know that this particular search will take
about 80 seconds to complete, I used the same parameters to test the
timeout feature of this web service routine (the code I posted above).
But...so I didn't have to agonizingly wait a full minute during my
testing, I just simply changed the CommandTimeout parameter to 5
seconds to get it to time out almost immediately and verify that the
timeout was working correctly.

I'm basically trying to execute the command, which of course is a call
to a stored procedure with the command parameters set appropriately.
If the command times out - in this example, in 5 seconds - I'd like to
cancel the command and return an error to the client notifying them
that the search timed out. And then, of course, if the command is
successful, I'd like to populate a DataSet object and return this
populated object as the return value of my function.

I know that there is only one matching record in the database, so upon
locating the record, it shouldn't take it but a microsecond to populate
the DataSet. But the command never times out after five seconds...I
put my watch on it and it takes up to the full 80 seconds before
control is returned to the next line of code after the line:

daRequest.Fill(dsReturnSet);

Hope that helps describe the problem in more detail...based on that,
any idea why the CommandTimeout would fail?

.

Relevant Pages

  • Re: DB Space
    ... > "Hari Prasad" wrote: ... >> Can you execute the below command to ghet the actual database size. ...
    (microsoft.public.sqlserver.msde)
  • Re: Set Restricted_User
    ... to the database, the connection may drop as the database ... the other users but for the app that executes the command to ... Then execute the command and then try ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Timeout
    ... If i increase .commandTimeout to 100, record will be added and database is resized.So it looks like Database resize is done first then record is added. ... Here is the question:For database Resize to happen why i need to increase timeout? ... How SQL Server resize and Client timeout is related? ... >> When I increased Command object timeoutto 120 seconds ...
    (microsoft.public.data.ado)
  • Get Table and Field Names
    ... To get a list of available tables in current database, ... execute the following command: ... EXEC sp_msforeachtable @command1='print REPLACE(REPLACE ... properties for a table, execute the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Enterprise Data Blocks and command timeout
    ... There is a command timeout and a connection timeout.. ... // Create a database object ... the command object timeout property when using the above code to ...
    (microsoft.public.dotnet.framework)