Using Temp Table as Source Error

S_Pete · July 13th, 2004, 04:02 PM

I have a DTS package on server A that calls a stored procedure on Server B (non-linked servers). The stored proc creates and returns data from a temp table. See the following for an example of the stored proc (very stripped down!):

Code:

CREATE PROCEDURE usp_temp
AS
create table #temp (item varchar(20))
insert #temp 
select item from mytable

select * from #temp 
drop table #temp

The trouble is when I call this stored proc in an Execute SQL Task (or Transform Data Task) it states that there is an Invalid Object Name: #temp. I read that stored procs that use temp tables cannot be used as the source for these objects, but it says I can use a global temp table. I tried changing the stored proc code to use ##temp, but to no avail. One further issue is that the agent account that will run the job will not have create rights on Server A, so a permanent table is not an option.

Thoughts?

bmains · July 14th, 2004, 05:02 PM

Hello,

I believe for table variables, that you need permission to tempDB. You can use a table variable, as such:

declare table @temp
(
item varchar(20)
)

insert into @temp
select item from mytable
...

Works the same, except table variables use memory allocations and that the stored proc cleans up the table variable when it stops running.

Brian

jbelina · July 15th, 2004, 07:44 AM

What version of SQL Server are you using? I was wondering if using a user defined function that returns a table variable would work for you. This would only work if you are using SQL 2000 on the server you are pulling data from.

Good luck!
Jeff

S_Pete · July 15th, 2004, 01:33 PM

I switched to using a table variable (@temp instead of #temp) and changed the maining code accordingly. Works fine now. According to Micr$oft, this is by design.

One other note for others...if attempting to execute this stored proc using VBScript (ActiveX) and inserting into a recordset, the line "SET NOCOUNT ON" must be added to the stored proc or the recordset will be closed! That took several hours to figure out! uggh.

bmains · July 15th, 2004, 03:17 PM

That's odd. I wonder if that is a known bug...