Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2008
    Posts
    6

    Question Unanswered: Moved servers: Now my Aggregate functions won't work

    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?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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?
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2008
    Posts
    6

    thanks for the note:

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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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!
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2008
    Posts
    6
    No problemo

  6. #6
    Join Date
    Mar 2008
    Posts
    6
    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?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by SEAQUIL
    Question: Are you the only one solving problems on this site? I see your name everywhere...
    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?
    George
    Home | Blog

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •