Cannot call SP in Execute SQL Task (DTS)

Question

Cannot call SP in Execute SQL Task (DTS)

Drew Salem

Ten Centuries

Points: 1060
More actions
December 16, 2005 at 8:11 am

#67120

It was fine. But now it's not!
I have a 2 line stored procedure that creates a temp table. If executed from Query Analyser (exec sp_temptable) it creates the table.
But when called from an Execute SQL Task task, it does not create anything, despite saying it was completed successfuly! AHHHH!
A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of the current session.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

ramses2nd Hall of Fame Points: 3720 More actions · Answer 1

Is the procedure creating a global or temporary table? In addition, take into account the following:

Drew Salem Ten Centuries Points: 1060 More actions · Answer 2

##temp_table.

I thought global tables remained until they were dropped. And if that's the case, why is it the table remains if created using Query Analyser?

ramses2nd Hall of Fame Points: 3720 More actions · Answer 3

The reason your global table would remain is that if you created the table using query analyzer it would not be dropped until that analyzer session is closed.