ADO.NET Timeout Exception - I have tried everything

S

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(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)
 
A

Alvin Bruney

have a look at your permissions. Does your asp.net process have permissions
to execute the stored proc? Are you doing impersonation or running on domain
controller etc? To figure out what is running as what, write some code to
dump the identity of the thread making the call. Then see if this identity
has the appropriate permissions.

--

________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
 
Joined
Feb 7, 2008
Messages
1
Reaction score
0
HI,

I had something similar and now I have it resolved. I had a timeout in ADO.NET when calling a stored proc that was not happening when calling it from Query Analizer.

My stored procedure was returning data from two tables through a "UNION ALL". Running it from SQLWB.exe with certain parameter values was working properly, but the same proc call with same parameters through ADO.NET was yielding a timeout.

Commenting out the first part of the UNION ALL was working OK in ADO.NET, commenting out the second part of the UNION was working also OK... so I thought there should be something wrong in the UNION ALL that made ADO.NET timeout.

By chance, I finally did some explicit cast and convert to all fields in both parts of the UNION and this resolved my timeout issue. The columns had compatible datatypes but maybe their length, precission was not the same and was preventing ADO.NET from reading the correct Metadata from the result set.

My original query...

SELECT a1, a2, a3 FROM Table1 UNION ALL SELECT b1, b2, b3 FROM Table2

was rewritten as...

SELECT
CONVERT(int, a1),
CONVERT(nvarchar(100), a2),
CONVERT(nvarchar(100), a3)
FROM
Table1
UNION ALL
SELECT
CONVERT(int, b1),
CONVERT(nvarchar(100), b2),
CONVERT(nvarchar(100), b3)
FROM Table2

...and that resolved my particular issue. I hope it helps.

Germán. Spain.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top