DTS and #temp tables

I have a rather complex SP that creates a #temp table and then populates tha
t
table using several select/inserts/update statements. I need the results of
this #table to be exported to external text file nightly so that our
mainframe FTPs can grab it.
DTS apparently wont let me use #temp tables. I get invalid object errors.
Ive heard of global ##temp, but even changing the table all the references
to be ##table isn’t solving the problem.
Is there commands I can place in the SP to create the external file without
using DTS, or can I define the #temp table in a way that DTS can see it?
Help appreciated
--
JP
.NET Software DeveloperJP,
You are running into problems with the scope of a #temp table. It is
available from the creating code level DOWN. Anything above the block of
code, stored procedure, etc will not be able to see this table. Likewise,
anything that runs in some other connection, for example one used by a DTS
package, will not be able to see the #temp table.
In the SQL Books Online, read the discussion under the CREATE TABLE command
on Temporary Tables. It discusses the scope and life of both kinds of
temporary tables.
RLF
"JP" <JP@.discussions.microsoft.com> wrote in message
news:66B3FF76-EB09-46A0-A9FF-2F6AA24E2EAB@.microsoft.com...
>I have a rather complex SP that creates a #temp table and then populates
>that
> table using several select/inserts/update statements. I need the results
> of
> this #table to be exported to external text file nightly so that our
> mainframe FTPs can grab it.
> DTS apparently wont let me use #temp tables. I get invalid object errors.
> Ive heard of global ##temp, but even changing the table all the
> references
> to be ##table isn't solving the problem.
> Is there commands I can place in the SP to create the external file
> without
> using DTS, or can I define the #temp table in a way that DTS can see it?
> Help appreciated
> --
> JP
> .NET Software Developer
>|||Hi,
I would suggest you to use UDF instead of #TempTable so it is able to be
exported
Ed
"JP" wrote:

> I have a rather complex SP that creates a #temp table and then populates t
hat
> table using several select/inserts/update statements. I need the results o
f
> this #table to be exported to external text file nightly so that our
> mainframe FTPs can grab it.
> DTS apparently wont let me use #temp tables. I get invalid object errors.
> Ive heard of global ##temp, but even changing the table all the reference
s
> to be ##table isn’t solving the problem.
> Is there commands I can place in the SP to create the external file withou
t
> using DTS, or can I define the #temp table in a way that DTS can see it?
> Help appreciated
> --
> JP
> .NET Software Developer
>