Temp and variable table cannot be created.

Groulien

So I've built a nice big Stored Procedure and call it from my SSIS package. My SP uses a temp table since it uses nestes SP's. Here's what I tries:

+------+-Table variable-+-Temp Table-+--Temp Table from SQL Server Management Studio--+
|Result|Failure | Failure | Success

It always fails unless I force a 'create table' from SQLSMS and keep SQLSMS open, the moment I close it the temp table gets dropped. What on earth is going on? I can only guess it's got to do with permission but I wouldn't really know where to look.

The first rule of CListCtrl is you do not talk about CListCtrl - kornman

Lost User

Your question is not clear. Where do you create the temp tables? In your SP or in SSMS ? Would you show us some code?

DaveAuld

Sounds like the account you are using from the SSIS package does not have the correct permissions compared to the account you are using while connected via SQLSMS. Have you added logging into your SP so any errors get written to the SQL Event Logger (there are maybe errors already being logged which will give you a clue).

Dave Find Me On: Web|Facebook|Twitter|LinkedIn

Folding Stats: Team CodeProject

Corporal Agarn

Try using ##yourtemptablename when creating the temp table. The ## makes the table usable by other sessions.

S Douglas

nbgangsta wrote:

My SP uses a temp table since it uses nestes SP's.

Where is the temp table being created at?

nbgangsta wrote:

the moment I close it the temp table gets dropped.

That is how temp tables work, as soon as the process that created the temp table completes and returns (exits scope) all temporary objects such as temp tables and variables get cleaned up (ie dropped) What are you trying to accomplish with the temp table? Return results to the SSIS package? I am guessing here that you are looking to parse the results of the stored proceedures in the SSIS package. -> In your SSIS pacakge -> Execute SQL Task -> General tab, Change ResultSet to 'Full Result Set' -> Result Set tab, 'Result Name' = 0 and 'Variable Name' = User::User_Defined (Variable of type object) -> Change stored proceedure

ALTER PROCEDURE test
...

-- Create a temp table to house the run info of the procs --
CREATE TABLE #Results(msg VARCHAR(200), LogTime SMALLDATETIME DEFAULT GETDATE())

-- Start time --
INSERT INTO #Results ("Start first child proc")
-- Execute the stored proc
EXEC sp_Test_First_Child_Proc
-- Completion Time --
INSERT INTO #Results ("Completed first child proc")
-- Start time --
INSERT INTO #Results ("Start second child proc")
-- Execute the stored proc
EXEC sp_Test_Second_Child_Proc
-- Completion Time --
INSERT INTO #Results ("Completed second child proc")

-- Ect.
...

-- Select the results from the temp table, this will return the result back to the SSIS package --
-- This is the important step, if you dont as a last step select the results out for return then SSIS will never the them and when the proceedure completes the #Results table will be dropped automaticly --
SELECT msg, LogTime FROM #Results

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.