Using a Global Temporary Table

[shaunk]

The first step of my package executes a stored procedure that creates a global temporary table as output.

The second step is a transform data task that selects the temp table as the 'Source' and updates an access database.

On selecting the 'Destination' tab, an ugly message is produced of the type 'deferred prepare could not be completed....invalid object name "##tempauto" (this is the name of the global temporary table).

The package runs, but I can't modify it.

There must be a better way to do this that can be maintained.

 

[mrdenny]

You have three ways to do this.

1. Use a perminant table. First create it, then load it up, then drop it.
2. Use a perminant table. Create it once. At the top of the package truncate the table, then load it.
3. To Edit the package, in a query Analyzer window create the global temporary table, you will then be able to edit the package.

The reason that you can't edit the package is because the table doesn't exist once the package has completed.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com