No Temp tables in SQLDatasource???

Please tell me there's something I haven't set. I've done several tests now. If your final return in a stored proc is from a temp table (ala #mytable ) the system cannot read the schema - for that matter, it won't run at all, complaining that the object #mytable doesn't exists.

It can't possible be that temp tables aren't allowed in procs used by SQLDatasource - please tell me what I am doing wrong.

This proc, when fed to a sqldatasource, fails in the designer with #temp does not exists.

CREATE PROCEDURE dbo.repTest_Temp
AS
BEGIN

CREATE TABLE #Temp
(
[iTestID] uniqueidentifier,
[bTest] [bit],
[cTest] [varchar]
)

INSERT INTO #Temp
SELECT *
FROM tTest

SELECT *
FROM #Temp

END

try:

CREATE PROCEDURE dbo.repTest_Temp
AS
BEGIN

CREATE TABLE #Temp
(
[iTestID] uniqueidentifier,
[bTest] [bit],
[cTest] varchar(10)
)

INSERT INTO #Temp
SELECT *
FROM tTest

SELECT *
FROM #Temp

END

It still won't read the schema, but you can do whatever you want to by manually entering the fields you need. I bound it to a gridview, and created 3 bound fields, adding the name of each field, and it showed up in my page just fine -- although I replaced the INSERT statement with "INSERTINTO #zzTemp(iTestID,bTest,cTest)VALUES(newid(),0,'test')"

|||

That's the same proc I posted - and no it won't load in a sqldatasource. I wasn't asking for a work around - I'm fully aware I can manually bind the grid. Kinda defeats the purpose of having a visual designer, don't you think?

What I asked was IF you can use a proc in a SQLDatasource that has it's final select from a temp table. I thought I made this clear by supplying the proc. If you can't, that's yet ONE more thing screwed up with the visual designer.

It's still blowing me away how useless the visual designer is to do any real work.

|||

I changed the sproc, if you notice the definition of one of your fields from [varchar] to varchar(10).

Let me try and make this clearer for you:

What I asked was IF you can use a proc in a SQLDatasource that has it's final select from a temp table. I thought I made this clear by supplying the proc.

What I answered was yes. You can use a proc in a SQLDatasource that has it's final select from a temp table. I thought I made this clear by supplying the proc I used, as well as how I implemented it, and got a gridview to databind to said sproc.

From your last message, you aren't interested in how, you just want to complain. I'm not here to make you feel better. I gave you a solution on how to get done what you needed, and it took me a total of 10 minutes. Take it and use it, or complain some more. I'm done with the thread.

|||

i realize this is considerably after the fact for the originating parties, i will still post my findings for anyone else that might experience this problem. i would define the problem as visual studio barking whenever i tried to drag a stored procedure to the .xsd designer interface which selected from a temp table. im posting here because this is the only thread i could find on the subject. i have rewritten the original posted procedure to show the solution. simply use a TABLE variable instead of a temp table.

CREATE PROCEDURE dbo.repTest_Temp
AS

DECLARE @.Temp TABLE (iTestID uniqueidentifier,
bTest bit,
cTest varchar(10))

INSERT @.Temp
SELECT *
FROM tTest

SELECT *
FROM @.Temp

this worked like a charm for me. hopefully someone else will find this information useful!

|||Another solution is to use Global Temp table instead of local temp table which goes out of scope quickly. ## global temp table and # local temp table. Hope this helps.