problem with no rows being returned - but rows are there

  • Thread starter Thread starter Marina Levit [MVP]
  • Start date Start date
M

Marina Levit [MVP]

Hi,

It seems that this issue is occurring at several client sites, though never
in house.

At some point, some (all?) queries run by our software start returning no
rows. But the rows are there in the database - and restarting IIS (this is a
web product) seems to resolve the issue. Even the following query:

SELECT SERVERPROPERTY('productversion')

Returns no rows under these conditions.

Like I said, restarting IIS, makes things go back to normal, and all the
queries being run by the .NET code are returning the expected results. There
is no apparent way to reproduce the problem or force it to happen.

There are no exceptions from the ADO.NET code - other then obviously our own
code, relying on certain pieces of data that isn't being retrieved for some
reason. No apparent problems opening connections or closing them, or running
the queries. Just

Anyone seen anything like this? Some sort of odd networking issue? Any
ideas?
 
Have you run a SQL Profiler trace to verify that your queries are
making it to SQL Server?

-Alan
 
Marina,

Just a first thought, are you sure that the session time out by those
clients is not somewhere on infinity (or like that). That a page who is
started comes unwanted in a kind of post back situation?

Just a guess, because that you said restarting the IIS wil help.

Cor
 
This is at client sites, so as you can imagine, it is difficult to get these
things done. I have asked for it, and still waiting for it.

It's hard to imagine that ADO.NET would just return an empty result set
instead of throwing an exception complaing of a connection problem or
whatever. Especially since as far as the code execution is concerned, the
connection gets opened succesfully.

I am suspecting some sort of network problem, maybe related to connection
pooling (since an IIS reset fixes things for a while). The hard part is,
it's impossible to tell what the problem is, or how to resolve it so that
the code can at least tell there is a network problem and handle things
appropriately.
 
We are not using ASP.NET session. We also don't use the ASP.NET model of
server side controls, or server forms, etc. It's basically a home grown
version of AJAX (before AJAX existed).

So this all happens in server side methods. The code is working just fine
with regards to having all the information to connect to the database, etc.
No exceptions of any kind.

It's just that it seems every query the application tries to run, results in
no rows.

My suspicion is that an IIS reset helps because it clears out the connection
pool, and that somewhere in there is the problem. However, that is just a
guess, and it doesn't provide a resolution with regards to graceful recovery
when this happens.
 
Marina,

The session is a part of IIS not only from ASPNET. It is widely used in ASP,
it keeps a user connected.

Cor
 
I guess this is kindof hard to phrase, but are you receiving an empty
result set (e.g., columns, but no rows) or are you uncertain that any
results are being returned? Is it possible that there could be a thread
synchronization issue causing concurrency issues in multiple threads
accessing stateful objects?

-Alan
 
Sorry Cor, I don't really follow how that is related to the problem I was
describing...
 
Not certain of anything. I know for sure no rows are returned. The error
message is 'invalid attempt to read when no data is present', which is the
message when trying to read out of a datareader that is not pointing to a
row. And of course we expect a query like "SELECT
SERVERPROPERTY('productversion')" to always come back with something.

All the code that is running is written synchronously. No threads being
created, nothing fancy.

This is only happening at 1 or 2 particular client site. Everyone else is
fine, running identical software and doing more or less similar things.

Also, user load doesn't seem to be a factor, as we were told this happens
during times when there is no heavy load or anything on the server.
 
Are you sure the error is coming from that part of the code? Put another
way, maybe the connection is bad, but error handling prevents an error from
being generated when you connect, or execute your SQL against a closed
connection.

If something goes wrong with your connections or your queries, and the error
is suppressed, then you will still get to the code where you try to process
the result set.

I know you said the connections are fine, but this is what it counts like to
me.
 
Is it possible that IIS is caching the pages, but the SQL is working fine?
So old pages are being served up?

What does the memory look like on these web servers when this happens?
 
Sounds like you're using a SqlDataReader. Is there any logic that takes
place the first time you call Read() on your SqlDataReader that may
cause it to fire more than once? I've occasionally seen, under certain
conditions, events firing multiple times, or being wired up multiple
times in ASP.NET. I'm totally blind to your implementation, but if
you're keeping anything open during the execution cycle of whatever
you're feeding to your AJAX stuff is it possible that an event could be
firing multiple times, thus trying to read something that's already
been read?

-Alan
 
I am positive that the connection is being opened. There is no error code
around connection opening. Besides, at that point, there would be a
nullreferenceexception on trying to access the datareader object - which is
not the case.

If there was an exception earlier on, then we would see whatever that is -
general network error, etc. The connection is definitely being opened
without any exceptions.
 
No, there is no such logic.

Like I said, this problem is easily remedied by an IIS reset.

So identical code - will run perfectly fine once the IIS reset has been
done.

If it were calling Read twice, then resetting IIS would be meaningless.
 
Nope, not possible. If the pages were being cached, then we wouldn't be
getting errors, since no actual code would be running. It would just server
up the last version.

From what I understand, there are no memory or CPU problems at the time this
occurrs. The only signal that there is an issue, is this behavior.
 
Also, please keep in mind, that these are only 1 or 2 clients out of
hundreds having this problem.

We can't reproduce this elsewhere, or in house, or at all on demand. It
happens seemingly randomly, and only to these clients. Everyone else is
fine, and everything is functioning perfectly given identical actions in the
software.
 
Okay... take a deep breath. Now, if something works on one system and not on
another what does that mean? To me it means that something is different
between the systems that work and those that don't. Of course I'm just
guessing, but do both systems (the ones that work and the ones that don't)
have the same build of everything? Are you sure? I would not be fiddling
around with the application to try to "fix" this problem as you'll likely
break everyone. I would focus on which version of the framework is
installed, what version of Windows, SQL Server (or Lord help you JET) is
installed. This could be a bad NIC card, a disk that's full, a bad network
cable or other hardware issue as well. I would catalog the two systems and
compare their configs to systems that work. I would take one of the working
systems and swap it with one that's failing. It might be the operator/user.
Perhaps they're pushing the wrong button in the wrong sequence. Perhaps
these users have MSN 8.0 beta installed and ... you get the idea....

As in a murder mystery the clues are often staring you in the face. You just
have to see them... ;)


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Marina,

Is it possible that somehow the "bad" connection ran a "SET FMTONLY ON"
transact SQL statement ? Can you run :

SET FMTONLY OFF
SELECT SERVERPROPERTY('productversion')

to see if that returns a result set ?

Stephen.
 
Marina,

Two in addition to Bill, first security settings and than IIS settings

Cor
 
This query (among others that have the same problem) are part of our
software. And due to the stacktrace, I can tell that this is the query
where this is happening.

it's not that someone is sitting there trying to run queries and getting
nowhere. It's that suddenly queries that are part of the product stop
returning rows.
 
Back
Top