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
>

Dts 2000 packages with SSIS

I need help,

I've got 51 pretty complex Dts packages that are running on Sql 2000. I'm
trying to make them run on Sql 2005 without at first migrating them with the
migration wizard, since i know that many of them cannot be migrated.

I'm using the "Execute DTS 2000 Package Task Editor" to make them run in an
SSIS package, some of them are running fine, but many of them, especially
those with a "Data Driven Query Task" are returning this error:

Error: System.Runtime.InteropServices.COMException (0x80040427): Execution
was canceled by user. at DTS.PackageClass.Execute() at
Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()

After many research on the web, i've reinstalled the Sql 2000 tools on the
Sql 2005 server, but i'm still receiving the same error. Also, i've tried to
repair the "Microsoft Sql Server 2005 backward compatibility" in the
Add/remove program of the Sql 2005 server without much success.

For your info, i've installed the hotfix Build 2153 on both the server and
the client and i'm still having the same problem......

Any idea?

Thanks.What happens when you just run them via dtsrun and/or the DTS designer?|||

They run fine without any errors!