Click here to Skip to main content
15,867,594 members
Home / Discussions / Database
   

Database

 
GeneralRe: Selecting multiple columns from several tables without using JOIN Pin
smcnulty200015-Jul-11 8:08
smcnulty200015-Jul-11 8:08 
GeneralRe: Selecting multiple columns from several tables without using JOIN Pin
AnnieMacD15-Jul-11 2:44
AnnieMacD15-Jul-11 2:44 
AnswerRe: Selecting multiple columns from several tables without using JOIN Pin
smcnulty200014-Jul-11 23:20
smcnulty200014-Jul-11 23:20 
QuestionTemp and variable table cannot be created. Pin
Groulien10-Jul-11 22:27
Groulien10-Jul-11 22:27 
AnswerRe: Temp and variable table cannot be created. Pin
Shameel11-Jul-11 1:42
professionalShameel11-Jul-11 1:42 
AnswerRe: Temp and variable table cannot be created. Pin
DaveAuld11-Jul-11 2:57
professionalDaveAuld11-Jul-11 2:57 
AnswerRe: Temp and variable table cannot be created. Pin
Corporal Agarn11-Jul-11 6:00
professionalCorporal Agarn11-Jul-11 6:00 
AnswerRe: Temp and variable table cannot be created. Pin
S Douglas11-Aug-11 10:07
professionalS Douglas11-Aug-11 10:07 
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
SQL
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.


QuestionRestoration of publisher database to another system (merge replication) Pin
Abd.Jabbar9-Jul-11 21:39
Abd.Jabbar9-Jul-11 21:39 
AnswerRe: Restoration of publisher database to another system (merge replication) Pin
Corporal Agarn11-Jul-11 3:28
professionalCorporal Agarn11-Jul-11 3:28 
QuestionWho owns the data/database? Customer or vendor? Pin
kmoorevs9-Jul-11 5:55
kmoorevs9-Jul-11 5:55 
AnswerRe: Who owns the data/database? Customer or vendor? Pin
Mycroft Holmes9-Jul-11 13:13
professionalMycroft Holmes9-Jul-11 13:13 
AnswerRe: Who owns the data/database? Customer or vendor? Pin
PIEBALDconsult10-Jul-11 6:32
mvePIEBALDconsult10-Jul-11 6:32 
GeneralRe: Who owns the data/database? Customer or vendor? Pin
kmoorevs11-Jul-11 9:14
kmoorevs11-Jul-11 9:14 
AnswerRe: Who owns the data/database? Customer or vendor? Pin
jschell12-Jul-11 11:08
jschell12-Jul-11 11:08 
GeneralRe: Who owns the data/database? Customer or vendor? Pin
kmoorevs13-Jul-11 5:03
kmoorevs13-Jul-11 5:03 
GeneralRe: Who owns the data/database? Customer or vendor? Pin
jschell13-Jul-11 9:05
jschell13-Jul-11 9:05 
GeneralRe: Who owns the data/database? Customer or vendor? Pin
smcnulty200014-Jul-11 23:04
smcnulty200014-Jul-11 23:04 
AnswerRe: Who owns the data/database? Customer or vendor? Pin
smcnulty200014-Jul-11 23:01
smcnulty200014-Jul-11 23:01 
GeneralRe: Who owns the data/database? Customer or vendor? Pin
kmoorevs15-Jul-11 6:06
kmoorevs15-Jul-11 6:06 
GeneralRe: Who owns the data/database? Customer or vendor? Pin
kmoorevs15-Jul-11 7:11
kmoorevs15-Jul-11 7:11 
Questiondeploying an SSIS package to server Pin
Alaric_7-Jul-11 8:23
professionalAlaric_7-Jul-11 8:23 
AnswerRe: deploying an SSIS package to server Pin
Alaric_7-Jul-11 16:09
professionalAlaric_7-Jul-11 16:09 
AnswerRe: deploying an SSIS package to server Pin
thatraja8-Jul-11 16:49
professionalthatraja8-Jul-11 16:49 
GeneralRe: deploying an SSIS package to server Pin
Alaric_11-Jul-11 2:24
professionalAlaric_11-Jul-11 2:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.