stored procedure not working in Execute Sql Tak

Question

stored procedure not working in Execute Sql Tak

preensheen

Ten Centuries

Points: 1362
More actions
October 26, 2010 at 9:59 am

#230370

Hi All,
I have stored procedure, When I run the SP in SSMS it works fine...but when I am trying to execute the same in execute sql task it is not... I am not able to understand the reason why?...I have set the result set to None. The SP runs even when I build the Query and it also shows green when I execute the task.
....But have a problem...
Since,
I am storing my result into a Globaltemp table in the SP, AND when I say Select * ##temp this works in SSMS AFTER i exec my SP.
But after i run my execute sql task and try to run the same select * ##temp..it doesn't show up any result instead it says we don't have any tempdb my tht name.
Plesae help me...i am totally lost thinkign where am I making the mistake.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

The reason why it works in SSMS is because your connection is still open, so you can still access the global temp table ##temp.

In SSIS however, the connection manager does not create a single connection, but it creates different connections for the various tasks that need those connections. You can see the connection manager as a factory that creates connections. What has this to do with temp tables? When you create the temp table in one task, the connection will already be closed when you arrive at another task. This has the consequence that the temp table is destroyed. If you do not want this, you must set the RetainSameConnection property of your connection manager to TRUE. This will ensure that the connection manager only creates one connection, so that your temp table will still exist when you need it.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

preensheen Ten Centuries Points: 1362 More actions · Answer 2

da-zero (10/26/2010)
The reason why it works in SSMS is because your connection is still open, so you can still access the global temp table ##temp.
In SSIS however, the connection manager does not create a single connection, but it creates different connections for the various tasks that need those connections. You can see the connection manager as a factory that creates connections. What has this to do with temp tables? When you create the temp table in one task, the connection will already be closed when you arrive at another task. This has the consequence that the temp table is destroyed. If you do not want this, you must set the RetainSameConnection property of your connection manager to TRUE. This will ensure that the connection manager only creates one connection, so that your temp table will still exist when you need it.

OMG..Thanks a lot, I was struggling hard.

It worked.:-):-):-):-)

Thanks,

preensheen

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Glad that it worked out.

We all have been struggling at some point with SSIS and temp tables...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP