TSQL And ASP - Store Procedures Help!!!!!!

All,
I have beating my head over this and have asked questions to clear this up in small bits.

In trying to get all of my business processes in stored procedures versus in the ASP pages. The ASP page is as follows:


--Normal ASP header
<%
myquery = "Exec MainSysLogQuery '6ED178C0-0202-4F8D-9C04-4C7B83A14190'

'Set Conn = Server.CreateObject("ADODB.Connection")'Set Connection Variable
Set SysMainQuery=Server.CreateObject("ADODB.Recordset" )
SysMainQuery.open "Select * from tbl_Date", strDSNPath

howmanyfields=SysMainQuery.fields.count -1

response.write "<table border='1'><tr>"

'Put Headings On The Table of Field Names
FOR i=0 to howmanyfields
response.write "<td NOWRAP><Font Size=2><b>" & SysMainQuery(i).name & "</b></td>"
NEXT
response.write "</tr>"

' Now loop through the data
DO WHILE NOT SysMainQuery.eof
response.write "<tr>"
FOR i = 0 to howmanyfields
fieldvalue=SysMainQuery(i)
If isnull(fieldvalue) THEN
fieldvalue="n/a"
END IF
If trim(fieldvalue)="" THEN
fieldvalue="&nbsp;"
END IF
response.write "<td valign='top' NOWRAP><Font Size=2>"
response.write fieldvalue
response.write "</td>"
next
response.write "</tr>"
SysMainQuery.movenext
'howmanyrecs=howmanyrecs+1
LOOP
response.write "</table></font><p>"

' close, destroy
SysMainQuery.close
set SysMainQuery=nothing
%>
</body></html>


The stored procedure takes the input an returns the sql statement:

Alter Procedure MainSysLogQuery
@myDates nvarchar(200) = '',
As
declare @oSql nvarchar(4000)
declare @viewtmp nvarchar(3000)
declare @querytmp nvarchar(3000)
declare @wheretmp nvarchar(3000)
declare @ordertmp nvarchar(3000)
declare @tmplen int

Set @wheretmp = ''
Set @tmplen = 0

if @myDates != ''
Set @wheretmp = @wheretmp + 'tbl_All_SysDATA.dateid = ''' + @myDates +''''

If len(@wheretmp)>0
Set @wheretmp = ' WHERE ' + @wheretmp

print @wheretmp
Set @viewtmp = 'SELECT *'
Set @querytmp = ' FROM tbl_All_SysData'
Set @ordertmp = ' ORDER BY LongDate DESC'
Set @oSQL = @viewtmp + @querytmp + @wheretmp + @ordertmp
PRINT @oSQL
Exec(@oSQL)
Go


The problem is I keep getting:
Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.

It returns the recordset when I call it from SQL Query Tool but not in the ADO object. If I use a regular SQL statement it works get but not with a Stored Procedure....
The Errors are on on the recordset.eof, recordset.movenext, etc....

Has anyone ever run into this?
Thanks
~Lee