Querying Temporary Tables From A Stored Procedure

What I am trying to do now is combine multiple complex queries into one table
and query it showing the results on an ASP.net page.


I am currently using SQL Server 2000 backend.  Each one of these queries are
pretty complex so I created each query as a Stored Procedure.  These queries are
dynamic by each user, so the results will never be the same globally.


What I have done so far was created a master stored procedure passing the
current user's username as a parameter.  Within the master SP (Stored Procedure)
it creates a temporary table inserts and executes multiple stored procedures and
inserts into the temporary directory.  Each of the sub stored procedures all
have the same columns.  After the insert to the temp tables, I then query the
temp table and return it to the function it was executed in code and fill it as
a System.Data.DataTable.  I then bind the DataTable to a
Repeater.DataSource.


Problem: When the page is rendered, it returns nothing.  I
tested the master SP in the data environment and it works fine.


Suspect: ASP.net when the SP is executed, it sees that the
data is a disconnected datasource?  Perhaps the session in the SQL Server when
the temp table is created isn't in SYSOBJECTS system table?


Here is an example of the code from the master SP:


CREATE PROCEDURE dbo.TM_getAlerts      @Username
varchar(32)ASCREATE TABLE #MyAlerts ([DATE] datetime, [TEXT]
varchar(200), [LINK] varchar(200) )

INSERT INTO #MyAlertsEXEC
TM_getAlertsNewTasks @Username

INSERT INTO #MyAlertsEXEC
TM_getAlertsLateTasks @Username

SELECT [DATE] AS 'DATE', [TEXT]
AS 'TEXT', [LINK] AS LINK FROM #MyAlerts

GO


It is a fairly simple call... but why doesn't ASP.net doesn't see it when the
the DataTable is filled.  I tried just executing one of the sub SP without
creating temporary tables and it works flawlessly.  But the query in one of the
sub SP is a normal but complex select.