temp tables in a DTS package

Question

Post reply

temp tables in a DTS package

Sam Greene

SSCertifiable

Points: 5252
More actions
December 22, 2006 at 9:04 am

#67899

Hi,
A developer is trying to call a stored proc from a execute sql task in a DTS package. Thus far, that is all he is doing - not manipulating the data in another step.
He is getting 'object #temptable is not a valid object' or something very similar to that. He said he also tried global table variables with no success.
If I (the dba) run it, it works.
Can anyone explain the cause of this?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Can we see the code of the procedure and how it is called?

MohammedU SSCoach Points: 19644 More actions · Answer 2

Use Global temp table (##)...

Create a ## global temp table in QA, without closing the QA window you have to create the dts package....

Once the dts package creation completed you can close the QA window...

Note: Temp tables are session specific.

MohammedU
Microsoft SQL Server MVP

Sam Greene SSCertifiable Points: 5252 More actions · Answer 3

I'll just explain what the proc does, it's pretty simple.

He creates two temp tables, union all 's them into another temp table and then does a query on that table using a sum operator on the amount column.

I've recommended that he get rid of the temp tables for now and just make one large sql statement. I don't know how that went - but that's normally what I do. But I'd still like to know why this doesn't work for him.

From the execute sql task in the dts he simply enters the procedure name. Of course, he also has a connection to his database in there, but that's it.

thanks!!

sam

MohammedU SSCoach Points: 19644 More actions · Answer 4

I resolved this many times in my place with the method which I explained...

If you don't need need temp table at all then you can get rid of it..

MohammedU
Microsoft SQL Server MVP

Sam Greene SSCertifiable Points: 5252 More actions · Answer 5

But what about when he drops them at the end of the DTS? I'm just not understanding why the proc will not run. The tables are not being used in the DTS in a later step. At execution time I'm assuming the DTS starts a session as the developer, when the procedure is called another session is started and the temp tables can only be 'seen' in the proc's session. I could understand the failure if the dts was trying to get at the results, but it's not...or is it?

Sam Greene SSCertifiable Points: 5252 More actions · Answer 6

I think they like temp tables to make things easier to understand.

Sam Greene SSCertifiable Points: 5252 More actions · Answer 7

When moving such a DTS to production will you have to perform this?

MohammedU SSCoach Points: 19644 More actions · Answer 8

Yes, when you are deploying dts to production if you need to redo mapping of columns.

MohammedU
Microsoft SQL Server MVP

Sam Greene SSCertifiable Points: 5252 More actions · Answer 9

Currently there is no mapping going on, he is simply calling the stored proc - this seems like it should work and is some kind of permission problem. Like I said - it works for me.