ADO.NET Timeout Exception - I have tried everything

[sorcerdon]

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(DataSetNa me, "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="cnITDevWin User" value="Data Source=server; Integrated
Security=SSPI; Initial Catalog=dbname; pooling=false;c onnection
reset=false;con nection lifetime=5;min pool size=1;max pool
size=10;connect ion 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)

[Erland Sommarskog]

(so*******@gmai l.com) 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(DataSetNa me, "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.Fil l 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.Fil l 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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Sorcerdon]

Erland,

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

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

[Erland Sommarskog]

Sorcerdon (so*******@gmai l.com) writes:

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

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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Sorcerdon]

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.