Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Invalid object Expand / Collapse
Author
Message
Posted Monday, September 6, 2010 7:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 10, 2011 5:57 AM
Points: 4, Visits: 15
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
Post #981107
Posted Monday, September 6, 2010 7:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
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...
Post #981108
Posted Monday, September 6, 2010 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 10, 2011 5:57 AM
Points: 4, Visits: 15
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
Post #981131
Posted Monday, September 6, 2010 8:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:17 AM
Points: 5,230, Visits: 9,457
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
Post #981141
Posted Monday, September 6, 2010 10:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
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

Post #981191
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse