Re: temp tables in stored procedures called from dts package

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: rmartinez (rmartinez_at_procard.com)
Date: 10/07/04 

Date: Thu, 7 Oct 2004 15:30:44 -0700

I have had the same issue, Even if you have a sqltask that
creates the table, the temp table is not available to the
dts package because it is created and dumped once the
connection is closed.
Is there some connection property to use to hold open the
connection?
>-----Original Message-----
>If you want to use a # table then what I do is I create
it inmy database
>just before I design the package so that it know about it
and can do all the
>maping things
>
>
>
>--
>
>Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>www.SQLDTS.com - The site for all your DTS needs.
>www.Konesans.com
>
>
>"matthew c. harad"
<matthewcharad@discussions.microsoft.com> wrote in
>message news:16F46593-7207-4D93-9AA5-
4F74682C163F@microsoft.com...
>> i'm calling a stored procedure to generate a dataset
for importation
>> across
>> sql servers. the stored procedure uses a temp table.
when trying to set
>> up
>> the dts package, i get an error message "invalid object
#temptablename"
>>
>> what's going on here? can i just not use temp tables
in sp's that get
>> called from dts packages? is there a standard
workaround? or am i just
>> doing something wrong?
>>
>> thanks
>> matthew
>
>
>.
>

Relevant Pages

  • Re: How to navigate recursive stored procedures results
    ... connection is inherited by the next user, ... beforehand so any residual #Temp tables would be dropped. ... the identity is not "flow"ing to the SQL Server - ... union a SQL query and the results of a stored procedure. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: My temporary table goes away too quickly
    ... > ODBC connection pooling, under the covers in the ODBC ... >>> the temp table outside of the stored procedure. ... >>> create procedure ptest ...
    (microsoft.public.sqlserver.programming)
  • Re: DataBase Refresh
    ... A temp table exists for the duration of a connection ... UNLESS it is created inside of a stored procedure, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: tempdb and #temp tables
    ... If you create a temp table inside a stored procedure, ... "Mario Splivalo" wrote in message ... So>> you would be able to see it, but you can't access anything in it (as EM has>> its own connection), hence cause a lot of confusion. ...
    (microsoft.public.sqlserver.server)
  • Re: My temporary table goes away too quickly
    ... I deduced from what Aaron said that this would not work, because each command execution through ADO is considered as a separate connection by SQL server. ... > the temp table outside of the stored procedure. ... > create procedure ptest ...
    (microsoft.public.sqlserver.programming)