Re: ADO.NET Timeout Exception - I have tried everything

(sorcerdon@xxxxxxxxx) writes:
Im a creating a data set and populating it with a call to a store proc.

Its a complex stored proc with the end result as an insert to a temp
table. Then I do a select from the temp table - in the store proc.


I get the following sqlException error on the following line:
DataAdapterName.Fill(DataSetName, "TableName")


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

How long time does take before you get the error? Since you say
that you've set the command timeout to 500, I expect it to take
three minutes, but I want to verify.

If I use a DataReader with the same storeProc, it works fine.

Since DataAdapter.Fill more or less is just a wrapper on ExecuteReader,
this is funky. (Then again, that sounds like you have a workaround.)

I get the feeling that there is a blocking issue lurking here. When
you run DataAdapter.Fill and are waiting for it to timeout, run sp_who
from Query Analyzer and look for non-zero values in the Blk column.
In such case the spid in the Blk column blocks the spid on this row.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Check Linked Table Connection
    ... That will only test to make sure the connection is available. ... Run the query with a 20 second timeout. ... but I don't have any direct access to their SQL Server nor their ... I am an outside programmer that end-users contract, ...
    (comp.databases.ms-access)
  • Re: Timeout Expired
    ... So the timeout occurs during the bulk load operations? ... did you check the SQL Error Logs to see more clues about the timeout problem if there is any? ... locked for DML operations by SQL Server. ...
    (microsoft.public.sqlserver.server)
  • Re: Increase Connection Command timeout globally
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... in Stored procs which is causing it to timeout. ... Connection and Command timeout settings are done on the client. ... I expect you'll have to visit each Command object declaration and set the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How does one set read-uncommitted on the entire DB?
    ... queries to determine if the locking will become less of a problem. ... All client APIs permit you to set the timeout, ... Note that query timeout is unrelated to SQL Server. ...
    (comp.databases.ms-sqlserver)
  • Re: SQL Timeout Errors - Can Anyone Help?
    ... The timeout period elapsed prior to completion of the ... SQL Server does not time out. ... and you get further blocking and timeout. ... There are two possible reasons why a query takes more than 30 seconds ...
    (comp.databases.ms-sqlserver)