?
Solved

.Net SqlClient Data Provider returns timeout for no reason

Posted on 2004-09-22
17
Medium Priority
?
984 Views
Last Modified: 2006-11-17
hello experts,
the following question i post into this category because i do not believe it's an SQL-mistake, but a .Net Data Provider-error.

did anyone of you working with .net c# and a sql-server using .Net SqlClient Data Provider run in the following problem - and best found a solution to the problem:

i have a not too big sql-statement with an inner-join and a group by. by testing it in sql-query-analyzer i get a result very quickly and with no costs. when i try to run the query in my app i only get a timeout-message after several long seconds from the .Net SqlClient Data Provider. the sql-profiler running beneath shows me that there is no sql-query given to the sql-server so it must be somehow lost in the data provider.

does anyone of you know a solution?
0
Comment
Question by:kolpdc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +2
17 Comments
 
LVL 4

Author Comment

by:kolpdc
ID: 12121021
the query is automatically generated. that causes the brackets.

SELECT a.KID, a.CID FROM (AAA a INNER JOIN BBB b ON a.ID = b.ID)
WHERE ((b.State LIKE N'test'))
AND   (((a.CID < 5000) OR (a.CID > 10000)))
GROUP BY a.ID, a.CID
ORDER BY a.ID
0
 
LVL 6

Expert Comment

by:wimthepimscake
ID: 12121249
I the connectionstring you are using the right one?
Can you post it?

Wim
0
 
LVL 4

Author Comment

by:kolpdc
ID: 12121278
that's the string
server=SERV;database=db;Persist Security Info=False;User Id=userid;Password=pwd

all my other queries work. only this one seems to bring me in trouble. first i thought, it came from a rosen amount of data in the server, but if there is no query send...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:wimthepimscake
ID: 12121311
So you say all the other queries work, so is the code u use to execute this one different from the other ones?

0
 
LVL 4

Author Comment

by:kolpdc
ID: 12121343
no, it's absolutely the same code.
i wondered if perhaps the query is too long, but i can't image that case at such a simple query.

the query works with the provider when following part is cut:
AND   (((a.CID < 5000) OR (a.CID > 10000)))

but like mentioned - only the data provider does not take it. in sql-query-analyzer it works well.
0
 
LVL 6

Expert Comment

by:wimthepimscake
ID: 12121429
I don't know then what the problem is, do you get an error of some kind in a try catch statement?
0
 
LVL 4

Author Comment

by:kolpdc
ID: 12121468
source:    .Net SqlClient Data Provider
message: timeout is ran out of time. the timelimit has been overstepped before process has finished or server does not react (translated from german).
trace:
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
0
 
LVL 6

Expert Comment

by:wimthepimscake
ID: 12121510
Can you try setting the TimeOut property of the command to a large number.
0
 
LVL 6

Expert Comment

by:wimthepimscake
ID: 12121525
Or try to put this in the connectionstring: Connection Timeout = 200
This does not work if there is a timeout specified on the SQL server, then that one overrides the connectionstring timeout.
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 12121902
Hi kolpdc,

It shouldn't make any difference but can you strip the brackets that are not required from the query as below and try that? I see it is generated automatically but perhaps it is worth a try.  I have padded the select columns so the total number of characters in the select is the same as before the brackets are removed.

One thing that looks odd, in the group by only "a.ID, a.CID" are specified this should probably be "a.KID, a.CID" shouldn't it?

SELECT a.KID, a.CID,1,1,1,1,1,1,1
FROM AAA a
  INNER JOIN BBB b ON a.ID = b.ID
WHERE b.State LIKE N'test'
  AND (a.CID < 5000 OR a.CID > 10000)
GROUP BY a.KID, a.CID
ORDER BY a.ID
0
 
LVL 10

Expert Comment

by:123654789987
ID: 12121952
Set the timeout property to a huge number say 10000. Or set it to a default value, which i think is 0 or -1
0
 
LVL 4

Author Comment

by:kolpdc
ID: 12122364
mcmonap, you only stripped the brackets, didn't you? 1,1,1 does not have a meaning.

123654789987, i think increasing the timeout is not the way to get my users satisfied. 30 seconds make them leave to get a coffee... ;)

i recognized, that it makes a difference, which server i am asking to deliver the data. like told - generally the query is ok. only if i fire the query from my app to the production-server (jippi!) it does not work. asking the development-server works fine and without problems.
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 12122419
Hi kolpdc,

you are correct, it has no meaning other than to pad the query so the text is the same length as with the brackets (thought this might discount the issue of whether it is the query that is too long...
0
 
LVL 4

Author Comment

by:kolpdc
ID: 12122545
i took a look at the length of the query and i recognized that there are much longer, much more complicated queries. so it shouldn't be the length.
like told between a very big amount of possible queries fired, it seems to be only this one with its modifications.
0
 
LVL 15

Accepted Solution

by:
mcmonap earned 2000 total points
ID: 12122565
So you can drop the 1,1,1... then did it work without the brackets?
0
 
LVL 2

Expert Comment

by:SimonTocker
ID: 12131070
IF the profiler is not showing it then it aint getting there,

try putting this query in a stored procedure and calling that see if it shows up in the profiler then or sorts your problem.

Then try calling the sp from query analyser and from your code and see what happens.

0
 
LVL 4

Author Comment

by:kolpdc
ID: 12131151
i can't believe it - mcmonap, you were right with your idea of cutting the brackets. but not all - only the ones that are not required.

instead of:      AND   (((a.CID < 5000) OR (a.CID > 10000)))
this works:     AND   ((a.CID < 5000) OR (a.CID > 10000))

could it be magic?!? since which point of time unrequired brackets produce a problem?!
-this problem happens only when asking a certain server. the other servers do not regret it. (same serversoftware, but other sp it seems)
-it only happens when called out of the application - not when called by query-analyzer.

thanks for your patience and your ideas.
kolpdc
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question