query doenst return any results

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that consults a simple table each 30 seconds, based on a
status field, through a storedproc. Almost all time the records in the table
remains the same. After a long time, about 24 hours, the query starts
returning no results, ie, when I query, the system return myreader.hasrows =
false, and even I insert another records it returns no results anymore. Then,
when I restart the application it starts returning all records again. Its not
a SQL Server problem, because when I use Query Analyser to run the stored
proc it returns all records.

Someone understands what is going on?
 
Sergio - are you using a try/catch block that may be eating the exception
and not reporting any results back? Also, the best way to determine what's
happening is to run a trace, that way you can see what's being sent to Sql
Server. if the query actually matches values, then they should get returned
so something else is happening. Something may be causing the paramater
values to change so the restriction may be causing nothign to be returned,
or like I said, a common cause of this symptom is a try catch block that
eats the exception and doesn't report back that something went wrong.
 
Ryan,
I actually use a try/catch block, but there are no exceptions, and using a
trace for 24 hours will be quite difficult. I think the key to find the
problem is that when I restart the application it starts returning results
again. But I dont know what is happening behind the scenes. I use always the
same connectionstring and stored proc, no new atributions. Why after a long
time it doesnt return the records?
 
Are you positive that exceptions aren't being thrown. There's nothing that
I know of (and ADO.NET has been around for a while so In all likelihood, it
probably would have come up) based on the symptoms you described. If you
can't run a trace for a prolonged period, I would at a minimum throw some
logging statements in the block to 1) be positive that no exceptions are
being thrown 2) the parameter values. Something is causing this so the only
way I know of to track it down is to try to isolate things.

Are you also 100% positive that you are closing your connections and/or are
they in a finally block so you're positive about it? I only ask b/c maybe
you're leaking connections - although that would result in an exception as
well.
 
Yes, there are no exceptions. The application has a log, but like I said,
there are no exceptions ... it simply doenst return any result anymore. I
have no parameter value, the stored proc simply have a "where status = 'R'"
clause. It seems that after a long time the dataadapter receives the same
result it cache it for lifetime. Is it possible? If I change to
oledbdataadapter do you think it will work?
 
Sergio - are you clearling the data table each time or is it just
repopulating it. I don't mean to sound dismissive of the possibility that
it's something on the db side, but the objects you're using are pretty well
tested so I'm inclined to think it's something else. I highly doubt that
changing to oledbdataadapter would change anything. Anyway, how are you
cacheing the data? Are you really confident that nothing else is changing
or changed - that the only thing different is the duration? Also, if you
would, post the code if you can, perhaps that might provide some insight.
 
This table receives messages that must be delivered when the destination get
connected. First the application get all undelivered messages, that almost is
the same for a long period, and tries to deliver verifying if the destination
is connected. It gets the messages, assign it to a dataset and release the
connection. Loop through the dataset, and after a message is delivered, the
application changes the status field of the message in the original table.
This table is accessed all time, to insert, to select and to update. Even
when the application cannot acess the data, query analyser can, what is
weard. First I thought that must a lock in database, then I made the dataset
to release fastly the connection, but it doesnt work. Im getting crazy with
this problem.
 
Sergio.

Just as idea, how do you set those 30 seconds. Is that a timer, which
restart in a time while meanwhile an operation is not yet finished, because
of a certain situation that seldom finds place by instance once in 24 hours?

Just a thought when I was reading your message.

Cor
 
Sergio - can you post the code snippet. Based on a description alone it's
very difficult to try to diagnose.
 
Yes, its a timer, but I disable it when the procedure starts, and then
restarts when the procedure ends.
 
Back
Top