Odd ASP buffer/cache problem?

I am working with code I did not write for a web app that used to run
on an Access database. Due to performance problems I'm trying to move
the app to a different server running SQL Server 2000 SP4, running on
Server 2003 with MDAC 2.8 SP2.
On the new server, one of the queries is failing in an odd way.
Roughly, the query is
select * from a, b where a.1 = b.1 and b.2 = x
This is old fashioned ASP code using ADO.Recordsets. One of the columns
I should be getting from the query is called "Number_Sections".
sql = the query
set cRS = dbCon.exectue( sql )
somevar = cRS("Number_Sections")
cRS("Number_Sections") is returning NULL even though the column in the
database is not null. If I run the query using the SQL Analyer on the
server itself, the column is indeed populated as I expect.
cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
also return NULL.
However, while trying to debug I threw in this code:
For zz = 0 to cRS.Fields.Count-1
response.write cRS.Fields.Item(zz).Name & " = " &
cRS.Fields.Item(zz).value & "<br>"
Next
After running through this loop, which prints out all 47 column names
and values *correctly*, cRS("Number_Sections") suddenly works. Take out
the loop and it stops working again.
This sounds like some kind of buffering problem to me, but I'm not
familiar enough with Microsoft products to know where I should be
looking for some setting to fix the problem. Is it ASP and IIS? Is it
SQL Server? I haven't been able to find any settings that look like
they address this issue and searching the MS Knowledge base hasn't
turned up anything helpful either.
Has anyone else seen this behavior? Can anyone point me in the right
direction to find a more graceful solution that leaving the for loop in
and setting the string to some dummy variable instead of sending it to
the response object?
Thanks.
-SeanStop using SELECT *. Do you really need 47 columns? If this column exists
in both a and b, then either alias it or only include it once. Another
thing you can try is somevar = cRS(n) where n is the 0-based ordinal
position of that column (which will be fun to figure out if you continue to
insist using SELECT *).
I have never seen this issue but without a better example, DDL/sample
data/code and a repro, it's tough to guess at what is happening.
<usenet@.dezynworks.com> wrote in message
news:1138651333.119837.167610@.f14g2000cwb.googlegroups.com...
>I am working with code I did not write for a web app that used to run
> on an Access database. Due to performance problems I'm trying to move
> the app to a different server running SQL Server 2000 SP4, running on
> Server 2003 with MDAC 2.8 SP2.
> On the new server, one of the queries is failing in an odd way.
> Roughly, the query is
> select * from a, b where a.1 = b.1 and b.2 = x
> This is old fashioned ASP code using ADO.Recordsets. One of the columns
> I should be getting from the query is called "Number_Sections".
> sql = the query
> set cRS = dbCon.exectue( sql )
> somevar = cRS("Number_Sections")
> cRS("Number_Sections") is returning NULL even though the column in the
> database is not null. If I run the query using the SQL Analyer on the
> server itself, the column is indeed populated as I expect.
> cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
> also return NULL.
> However, while trying to debug I threw in this code:
> For zz = 0 to cRS.Fields.Count-1
> response.write cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).value & "<br>"
> Next
> After running through this loop, which prints out all 47 column names
> and values *correctly*, cRS("Number_Sections") suddenly works. Take out
> the loop and it stops working again.
> This sounds like some kind of buffering problem to me, but I'm not
> familiar enough with Microsoft products to know where I should be
> looking for some setting to fix the problem. Is it ASP and IIS? Is it
> SQL Server? I haven't been able to find any settings that look like
> they address this issue and searching the MS Knowledge base hasn't
> turned up anything helpful either.
> Has anyone else seen this behavior? Can anyone point me in the right
> direction to find a more graceful solution that leaving the for loop in
> and setting the string to some dummy variable instead of sending it to
> the response object?
> Thanks.
> -Sean
>|||First, what you are describing sounds like an ASP coding issue, and not a
SQL Server issue. You would have better luck posting on an ASP newsgroup.
However, if you post more of your code, starting with where you define
dbCon(less the actual connection string of course), and ending with closing
your connection, folks will be able to offer more advice.
It sounds to me like you are not properly looping through your dataset, but
I can only guess without seeing the actual code.
<usenet@.dezynworks.com> wrote in message
news:1138651333.119837.167610@.f14g2000cwb.googlegroups.com...
> I am working with code I did not write for a web app that used to run
> on an Access database. Due to performance problems I'm trying to move
> the app to a different server running SQL Server 2000 SP4, running on
> Server 2003 with MDAC 2.8 SP2.
> On the new server, one of the queries is failing in an odd way.
> Roughly, the query is
> select * from a, b where a.1 = b.1 and b.2 = x
> This is old fashioned ASP code using ADO.Recordsets. One of the columns
> I should be getting from the query is called "Number_Sections".
> sql = the query
> set cRS = dbCon.exectue( sql )
> somevar = cRS("Number_Sections")
> cRS("Number_Sections") is returning NULL even though the column in the
> database is not null. If I run the query using the SQL Analyer on the
> server itself, the column is indeed populated as I expect.
> cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
> also return NULL.
> However, while trying to debug I threw in this code:
> For zz = 0 to cRS.Fields.Count-1
> response.write cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).value & "<br>"
> Next
> After running through this loop, which prints out all 47 column names
> and values *correctly*, cRS("Number_Sections") suddenly works. Take out
> the loop and it stops working again.
> This sounds like some kind of buffering problem to me, but I'm not
> familiar enough with Microsoft products to know where I should be
> looking for some setting to fix the problem. Is it ASP and IIS? Is it
> SQL Server? I haven't been able to find any settings that look like
> they address this issue and searching the MS Knowledge base hasn't
> turned up anything helpful either.
> Has anyone else seen this behavior? Can anyone point me in the right
> direction to find a more graceful solution that leaving the for loop in
> and setting the string to some dummy variable instead of sending it to
> the response object?
> Thanks.
> -Sean
>