ADO.NET Timeout Exception - I have tried everything

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)Another thought: Go to see the "SQL Server Logs" or the "Event Viewer" on th
e
Server machine to see if there is error occurred on hardware. Sometimes a
certain bad sector on disk can cause SQL Server timeout.
Just my 2 cents.
James
"sorcerdon@.gmail.com" wrote:

> 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)
>|||Hi:
Check the memory usage/settings on the server. I encountered timeout
errors when the server ran out of memory on a complicated query that
was run repeatedly. Check the Event Log for 'Out of Memory' errors
(code 123, I believe).
- David
sorcerdon@.gmail.com wrote:
> 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)|||Hi:
Check the memory usage/settings on the server. I encountered timeout
errors when the server ran out of memory on a complicated query that
was run repeatedly. Check the Event Log for 'Out of Memory' errors
(code 123, I believe).
- David
sorcerdon@.gmail.com wrote:
> 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)