#Temp table as ADO Recordset

#Temp table as ADO Recordset

Post by Rick » Sat, 25 Sep 1999 04:00:00



I've got a stored proc that needs to do a SELECT / UNION and order the
result set.  I think the only way to order it is to output the results to
another table then SELECT the other table with an ORDER BY.  For this
purpose, I'm using a #Temp table, and this works fine under query analyzer.

Having a problem on the client side, though.  Using ADO2.1 / SQLOLEDB to
execute the command to an ADO recordset.  The command executes without
error, but the recordset object doesn't get or stay instantiated.  However,
the recordset works fine when I comment out the #Temp table processing
(although the results aren't ordered, of course).

I've done things like this before with VB's RDO2 without any problems.  Is
there something I need to know about ADO and Temp Tables?

Here's some VB code I can recreate the problem with.

Sub GetEventDisplayList(lngPromoTempID As Long)
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strSQL As String
Dim strDate As String

strDate = Format(Now, "yyyy-mm-dd")
strSQL = "GetEventsWithInventory"

Set conn = GetConnection  'function to create a new connection
Set cmd = New ADODB.Command
Set parmPromoTemplateID = New ADODB.Parameter
Set parmEventDate = New ADODB.Parameter

cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = strSQL

Set rs = cmd.Execute

if rs.eof then
  'it worked
end if

End Sub

 
 
 

#Temp table as ADO Recordset

Post by Jeff Grei » Sat, 25 Sep 1999 04:00:00


You don't need the temp table.  You can treat the result of the union as a
derived table, and perform the ordering on that, viz:

select A,B,C from
   (select A,B,C from x ...
    union
    select A,B,C from y ...) u
order by A,B

Jeff


Quote:> I've got a stored proc that needs to do a SELECT / UNION and order the
> result set.  I think the only way to order it is to output the results to
> another table then SELECT the other table with an ORDER BY.  For this
> purpose, I'm using a #Temp table, and this works fine under query
analyzer.


 
 
 

#Temp table as ADO Recordset

Post by JRSte » Tue, 28 Sep 1999 04:00:00


You don't even need the outer select.

Whether that's kosher standard or not, I don't know.

Joshua Stern

On Fri, 24 Sep 1999 18:47:45 GMT, "Jeff Greif"


>You don't need the temp table.  You can treat the result of the union as a
>derived table, and perform the ordering on that, viz:

>select A,B,C from
>   (select A,B,C from x ...
>    union
>    select A,B,C from y ...) u
>order by A,B