asked on

.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?

kolpdc

ASKER

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

wimthepimscake

I the connectionstring you are using the right one?
Can you post it?

Wim

kolpdc

ASKER

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...

wimthepimscake

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

kolpdc

ASKER

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.

wimthepimscake

I don't know then what the problem is, do you get an error of some kind in a try catch statement?

kolpdc

ASKER

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)

wimthepimscake

Can you try setting the TimeOut property of the command to a large number.

wimthepimscake

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.

mcmonap

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

123654789987

Set the timeout property to a huge number say 10000. Or set it to a default value, which i think is 0 or -1

kolpdc

ASKER

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.

mcmonap

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...

kolpdc

ASKER

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.

ASKER CERTIFIED SOLUTION

mcmonap

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SimonTocker

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.

kolpdc

ASKER

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