Hello!
I am looking for someone who has solved this multi-million people's
problem. EVERYONE seems to ahve this problem.

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.


My connectiong string looks like this:
<add key="cnITDevWinUser" value="Data Source=server; Integrated
Security=SSPI; Initial Catalog=dbname; pooling=false;connection
reset=false;connection lifetime=5;min pool size=1;max pool
size=10;connection timeout=120" />


I have admin rights on that db.


I have set my command.timeout to 500.


If i run this same code in a windows application, it works fine.
If I use a DataReader with the same storeProc, it works fine.
If I run this same code on a simple selec (hello world), it also works
fine.


If I run this store proc in QueryAnalyzer it works fine and is done
within 6 seconds.
If I run this on a different machine it produces the same result.


I am using SQL2000 with vb.net in VS2003.


I have looked everywhere for the answer. I can't find it anywhere.
PLEASE SOMEONE HELP.


regards,
Stas K.(a.k.a Sorcerdon)

(sorcerdon@gmail.com) writes:
[quoted text, click to view]

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.

[quoted text, click to view]

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@sommarskog.se

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

Erland,

Already done that. There are no locks.
I forgot to mention that in the original post.

regards,
Stas K (a.k.a Sorcerdon)

Sorcerdon (sorcerdon@gmail.com) writes:
[quoted text, click to view]

I afraid then I don't have more suggestions with the information you have
given.

You could use Profiler to see if you get different query plans for
the different situations, althogh I don't see why that would happen.

Then again, there may be some pertinent information you have shared with
us.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

The problem is solved but a new mystery begins.
The problem was one of the parameters being passed is wrong.
but the mystery is that sql didnt return an error - it just froze....
intresting.