Solved

.Net SqlClient Data Provider returns timeout for no reason

Posted on 2004-09-22
17
975 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
  • 7
  • 5
  • 3
  • +2
17 Comments
 
LVL 4

Author Comment

by:kolpdc
Comment Utility
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
Comment Utility
I the connectionstring you are using the right one?
Can you post it?

Wim
0
 
LVL 4

Author Comment

by:kolpdc
Comment Utility
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
 
LVL 6

Expert Comment

by:wimthepimscake
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Can you try setting the TimeOut property of the command to a large number.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 6

Expert Comment

by:wimthepimscake
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
So you can drop the 1,1,1... then did it work without the brackets?
0
 
LVL 2

Expert Comment

by:SimonTocker
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

728 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now