sqlDataReader does not return correct sorted data

[Sam]

Hi All,

I have a very strange issue with ms sql stored procedure and sqlDataReader
and I hope that someone can tell me what's going on . I have an asp.net
application which has one of its page populated with data onto a datagrid
and recently the records are not sorted correctly on the datagrid(we do all
the sorting in our stored procedure and no sorting on the code behind page).

The strange things is that:

1. It doesn't happen all the time. Some days it does and some it does not
and usually in the morning when we have a high traffic.
2. And while this sorting issue appeared on the page, I ran the stored
procedure that pulls data for the grid with the query analyzer, I get
correct sorted records
3. The third thing is that, when this strange thing happens, if I use the
sql enterprise manager to open the stored procedure and click on ok button,
the datagrid on the page gets correct sorting again and this last for about
couple of days or so. This seems to me that there is something on the sql
server which gets refresh when I click on the OK after viewing the stored
procedure.

Does anyone experience something similar or know where I should looking for
errors? Thanks in advance for your help.

Regards,

Sam

 

[Guest]

Sam,

I was wondering if you are caching the data shown with the datareader.
The page might not be getting data from the database it could showing data
stored in memory which was sorted.

http://msdn2.microsoft.com/en-us/library/6hbbsfk6.aspx

Ken

 

[susiedba]

use an order by in your sql statement, maybe?

sql statements without an order by will frequently provide an
inconsistent sort order

-Susie, DBA

 

[Sam]

Hi Ken,

Thanks for your suggestion. I went through the aspx page(s) and I don't see
any places that cache data from the data reader.