views:

35

answers:

0

I'm running the following query in a PHP ODBC connection to a MSSQL sever:

DECLARE @weekStart SMALLDATETIME;
SET     @weekStart = DATEADD(d,0, DATEDIFF(d,0,GETDATE()));             -- weekStart = TODAY @ 00:00:00 -- per http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
SET     @weekStart = DATEADD(d, 1-DATEPART(dw, GETDATE()), @weekStart); -- subtract to last SUN @ 00:00:00

SELECT
    DATEDIFF(week, Run_Date, @weekStart)
                            AS weeksAgo

    ,MIN(Current_List)      AS list

    ,COUNT(*)               AS cnt

    ,SUM(NoContact_90Days)  AS noContact90
    ,SUM(NoContact_180Days) AS noContact180

    ,SUM(NoMtg_180Days)     AS noMtg180
    ,SUM(NoMtg_360Days)     AS noMtg360
FROM
    [someDB].[DBO].[someTable]
WHERE
    Current_List<>''
GROUP BY
    DATEDIFF(week, Run_Date, @weekStart)
    ,Current_List
ORDER BY DATEDIFF(week, Run_Date, @weekStart)

;

Somehow when I run this through my connection, it seems to "ruin" the connection for further queries - that is to say, if the above query is Q1, and some other trivial query is Q2, then the following works fine:

$rs = odbc_exec($conn, $Q2);
var_dump($rs);  //  $rs is a valid result set

but this fails without an error:

$rs1 = odbc_exec($conn, $Q1);

$rs2 = odbc_exec($conn, $Q2);
var_dump($rs2);  //  $rs2 is FALSE

Nothing in Q2 is changed above between the two examples, so it seems that Q1 is somehow "spoiling" the connection.

I've been playing with this for a couple of days now, and I have run into a couple of things which lead me to think that it's because Q1 is returning more than one result set, but I'm not quite sure what that actually means.

Here are my questions:

  1. What does it mean for an ODBC connection to return more than one result set?
  2. Is there any way to get this to work?

Thanks!


Edit: Additional question

  1. When dealing with multiple record sets being returned, am I guaranteed that they will be returned in the same order? Or is it possible that the DBMS might send back (rs1, rs2, rs3) one day and (rs3, rs1, rs2) the next?