Global Temp Table

• Pulivarthi Sasidhar

  SSCertifiable

  Points: 6706

  More actions

  February 13, 2015 at 3:15 am

  #297661

  Hi ,

  On Serever:-

  I have created a global temp table in Step1 of SQL Job.

  I have used that in remaining steps of same job...i ran the job

  But i got error message like invalid object name ##xxxxxxxx later i have included as tempdb..##xxxxxxxx also. the i got invalid reference for...

  From my SSMS:-

  But i was able to do select query for the same from my SSMS...

  i have incorporated all steps in single step and completed job...

  My question is why ##temp table created in step1 is not able to use in other steps of same job ?

  SQL Server 2012 Enterprise Edition

  Thanks,

  Sasidhar Pulivarthi

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  February 13, 2015 at 4:29 am

  #1777468

  A global temp table is dropped as soon as the connection which created it closes and no one else is referencing it. Hence when step 1 of the job finished, the connection was closed and because no other session was using your global temp table, it was automatically dropped.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 13, 2015 at 6:48 am

  #1777497

  if you create table in tempdb, without the # or ##, it will stick around until the server is stopped and started:

  IF OBJECT_ID('[tempdb].[dbo].[Facility]') IS NOT NULL

  DROP TABLE [tempdb].[dbo].Facility

  Create Table [tempdb].[dbo].Facility (

  Facility int ,

  FacilityName varchar(100))

  then you would wantto reference ti with explicit three part naming conventions.

  SELECT * FROM [tempdb].[dbo].Facility

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!