InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 08:07:22 GMT20http://www.sqlservercentral.com/Forums/Topic981107-149-1.aspx[quote][b]sreecareer (9/6/2010)[/b][hr]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.[/quote]Not entirely true.. Dynamic queries can refer a temp table created in the parent session.. Surprised? Here is a code snippet to check it :[code="sql"]SET NOCOUNT ON DECLARE @Dyn_SQL VARCHAR(1000)SET @Dyn_SQL = ''IF OBJECT_ID('tempdb..#Temp') IS NOT NULLDROP 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 cteSELECT @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[/code]Mon, 06 Sep 2010 10:47:26 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic981107-149-1.aspxSreePlease 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.ThanksJohnMon, 06 Sep 2010 08:42:53 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic981107-149-1.aspxThank 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. ThanksSreeMon, 06 Sep 2010 08:14:39 GMTsreecareerhttp://www.sqlservercentral.com/Forums/Topic981107-149-1.aspxLook, 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...Mon, 06 Sep 2010 07:27:26 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic981107-149-1.aspxHi,I am using a temp table in my sp. I have written the following tsql before and after using the temp table.[code="sql"]IF OBJECT_ID('tempdb..##TEMP') IS NOT NULL DROP TABLE tempdb..##TEMP;[/code]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 SreeMon, 06 Sep 2010 07:20:04 GMTsreecareer