Invalid object

Question

Invalid object

sreecareer

SSC Enthusiast

Points: 114
More actions
September 6, 2010 at 7:20 am

#146830

Hi,
I am using a temp table in my sp. I have written the following tsql before and after using the temp table.
IF OBJECT_ID('tempdb..##TEMP') IS NOT NULL DROP TABLE tempdb..##TEMP;
Data will be inserted to the temp table consecutively using a cursor. I use SELECT * INTO tempdb..##TEMP FROM EXEC('query') to fill this temp table for the first time and INSERT INTO tempdb..##TEMP(<fields>) EXEC('query') for the remaining times.
The SP is working fine in my laptop, but I am getting an Invalid Object error on this temp table name in the production server. Could anyone tell me the possible causes please?
Thanks
Sree

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

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

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 1

Look, you are creating a GLOBAL temp table (## = Golabal, # = Session-based).. this has visibility globally.. so there may be some other code in your PROD system that is dropping this table.. change your code to use normal temp table, then u wont face this issue...

sreecareer SSC Enthusiast Points: 114 More actions · Answer 2

Thank you for the quick reply...

I think, I need a global temp table because I am using EXEC('query') also in the SP to insert to the temp table. If we use EXEC, I think, it will create a local connection again, and so the local temp table will not be available in that session.

Anyway, when I am using local temp table, it is throwing the Invalid object name error in my laptop itself.

Thanks

Sree

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Sree

Please will you post your query so that we can see whether there is any better way of doing this. I don't think your SELECT INTO syntax is correct.

Thanks

John

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 4

sreecareer (9/6/2010)
Thank you for the quick reply...
I think, I need a global temp table because I am using EXEC('query') also in the SP to insert to the temp table. If we use EXEC, I think, it will create a local connection again, and so the local temp table will not be available in that session.

Not entirely true.. Dynamic queries can refer a temp table created in the parent session.. Surprised? Here is a code snippet to check it :

SET NOCOUNT ON

DECLARE @Dyn_SQL VARCHAR(1000)

SET @Dyn_SQL = ''

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL

DROP TABLE #Temp

; with cte (N) as

(

select 1 n union all

select 1 n

)

SELECT N into #Temp FROM cte

SELECT @Dyn_SQL = 'SELECT N FROM #Temp ; UPDATE #Temp SET N = N + 1 ; SELECT N FROM #Temp ; DROP TABLE #Temp '

EXEC (@Dyn_SQL)

SELECT N FROM #Temp