September 6, 2010 at 7:20 am
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
September 6, 2010 at 7:27 am
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...
September 6, 2010 at 8:14 am
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
September 6, 2010 at 8:42 am
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
September 6, 2010 at 10:47 am
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 union all
select 1 n union all
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply