Okay, this is weird!

I had a (ASP - VB Script) web application sitting on a Windows 2000 box with SQL Server 2005 as the backend.

Last month, we moved to a Windows 2003 machine, all the code stayed the same, as well as the backend.

The PROBLEM: Aggregate sql statements are not executing!

SELECT Sum(CarCost) as TheCost from Automotive;

This query returns no record. When I run it directly through the backend on SQL Server 2005, I get the amount I'm looking for.

So then I started doing more testing....Count(*), Sum(), Avg(), etc...none of them work...but a standard select, does.

Any clue folks?

Moving this to the ASP topic as it doesn't appear to be an SQL Server problem.


...Can you post an example of some offending ASP code?

here she blows:

<!-- #INCLUDE FILE="../../../global.asp" -->
<!-- #INCLUDE FILE="Utilities.asp" -->

<%

Server.ScriptTimeOut = 60 * 20 '20 minutes
connIPO.open provIPO

Insert

Sub Insert()

dim sql
sql = "SELECT fType FROM tPortTypes WHERE fPortID = 7 AND tPortTypes.fPortTypeID <> 496"

Response.Write("<BR><BR>" & sql & "<BR><BR>")

Set rsi = Server.CreateObject("ADODB.Recordset")
On Error Resume Next

rsi.open sql, connIPO, 3, 3
intRows = rsi.RecordCount
response.write("<b><font color=blue>NO ROWS? " & intRows & "</font></b>")

if err.number <> 0 then
response.write ("error in INSERT sub: ")
response.write "uggggh: #" & Err.Number & ".\nError Message: " & Err.Description & ".\nHelp Context: " & Err.HelpContext & ".\nError Source: " & Err.Source & "."
end if

If intRows < 1 Then
response.write("<b>NO RECORDS RETURNED</B>")
'THIS IS PRINTED, AND THERE IS NO RECORDS BEING PASSED HERE, YET, PULLING THE QUERIES AND RUNNING THEM PROVES SUCCESSFUL
rsi.Close
Set rsi = Nothing
Exit Sub
End If

While Not rsi.EOF
strType = rsi.Fields("fType")
response.write("yes: " & strType & "<br>")
rsi.MoveNext
Wend

rsi.Close
Set rsi = Nothing

End Sub
%>

My first guess is that the recordset object is not being created; you're going to have to do a bit more debugging methinks matey!

If you are sure the object is being instantiated then the next one to check is whether your connection is opening correctly; I imagine your conection string has changed since you've moved servers/

Final thought: please do not post the same question more than once - post a single one where you deem most appropriate and we'll move it if we think it sits somewhere better

Good luck!

No problemo

Question: Are you the only one solving problems on this site? I see your name everywhere...

Here's a follow up to my problem, the connection string works great for everything else.

A simple select query prints data to the screen in a snap, so the object is being created...

I'm trying to get down to business by asking "Why won't aggregate functions work all of a sudden?"

Anyone else want to take a CLOSER look?

Haha, nope, but as you can see I am quite prolific

Well in your code posted above you wern't using an aggregate function; hence my initial questionning!

So you've proved that you're opening a recordset... What happens when you test for EOF / BOF?

Sounds like a job for profiler. Find out what's actually being sent to the server you so you can ensure you're requesting and receiving the correct data. Work from there.