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