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

[Ken Tucker [MVP]]

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

"Sam" wrote:

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

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

"Ken Tucker [MVP]" <Ke**********@discussions.microsoft.comwrote in message
news:C1**********************************@microsof t.com...

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

"Sam" wrote:

>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

[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 wrote:

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