.Net SqlClient Data Provider returns timeout for no reason

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?
LVL 4
kolpdcAsked:
Who is Participating?
 
mcmonapCommented:
So you can drop the 1,1,1... then did it work without the brackets?
0
 
kolpdcAuthor Commented:
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
 
wimthepimscakeCommented:
I the connectionstring you are using the right one?
Can you post it?

Wim
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
kolpdcAuthor Commented:
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
 
wimthepimscakeCommented:
So you say all the other queries work, so is the code u use to execute this one different from the other ones?

0
 
kolpdcAuthor Commented:
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
 
wimthepimscakeCommented:
I don't know then what the problem is, do you get an error of some kind in a try catch statement?
0
 
kolpdcAuthor Commented:
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
 
wimthepimscakeCommented:
Can you try setting the TimeOut property of the command to a large number.
0
 
wimthepimscakeCommented:
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
 
mcmonapCommented:
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
 
123654789987Commented:
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
 
kolpdcAuthor Commented:
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
 
mcmonapCommented:
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
 
kolpdcAuthor Commented:
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
 
SimonTockerCommented:
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
 
kolpdcAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.