Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Invalid object name '#Temp'

Answered Invalid object name '#Temp'

  • Friday, January 18, 2013 8:44 PM
     
     
    Sign In to Vote
    0

    I have a querey  below


    IF OBJECT_ID('tempdb..#Actives', 'U') IS NOT NULL DROP TABLE #Actives
    select cm.FirstName, cm.LastName, cm.EMail, cm.CustNum, si.BOSiteID as SiteID
    into #Actives
    from warehouse.dbo.MembershipCust mc (nolock)
    join WareHouse.dbo.SiteDim si (nolock) on mc.CurrSiteId=si.SiteID
    join warehouse.dbo.CustomerDim cd (nolock) on cd.CustID=mc.CustID and mc.SourceSystemID=cd.SourceSystemID
    join Reporting.dbo.CustMaster cm (nolock) on cm.CustNum=cd.CustNo
    where mc.MembershipStatusID=51
    and
    (
    (si.BOSiteID=100302 and mc.CurrSiteversionId in ( 957, 963, 1001))
    or (si.BOSiteID=100322 and mc.CurrSiteversionId in (872))
    or (si.BOSiteID=100330 and mc.CurrSiteversionId in (940))
    or (si.BOSiteID=100331 and mc.CurrSiteversionId in (948))
    or (si.BOSiteID=100341 and mc.CurrSiteversionId in (986))
    or (si.BOSiteID=100342 and mc.CurrSiteversionId in (995))
    )

    Gets inserted to #Actives Sucessfully bur when i try the next script

    IF OBJECT_ID('tempdb..#Actives2', 'U') IS NOT NULL DROP TABLE #Actives2
    select distinct a.*
    into #Actives2
    from #Actives a
    join Reporting.dbo.MarketingInfo mi (nolock) on a.firstname=mi.FirstName and
        a.lastname=mi.LastName and a.email=mi.EMail and a.siteid=mi.siteid and mi.GetPromoEmail=1

    I get Invalid object name '#Temp':Msg 208, Level 16, State 0, Line 1

    I am using same session running one querey after the other  , i see same spid for both statements

    Can any one please help me on this

     

All Replies

  • Friday, January 18, 2013 8:47 PM
    Moderator
     
     
    Sign In to Vote
    0

    The error #Temp does not match the name #Actives you're using. Also, both scripts should be in the same connection otherwise the #Actives table will not be visible.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Friday, January 18, 2013 8:51 PM
     
     
    Sign In to Vote
    0

    sorry i copied the wrong error ,
    I get Invalid object name '#Actives':  Msg 208, Level 16, State 0, Line 1

    both scripts should be in the same connection otherwise the #Actives table will not be visible.:meaning i need to run both queries at a time ?

    Please let me know

     
  • Friday, January 18, 2013 9:17 PM
    Moderator
     
     
    Sign In to Vote
    0
    Yes, run both of these statements using the exact same connection and better using the same batch.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Friday, January 18, 2013 11:01 PM
     
     Answered Has Code
    Sign In to Vote
    1

    Scopes/Sessions are an interesting thing in sql server.They follow the rule of inheritance, same as transactions:
    A child has all parents' "objects" plus it's own. So what this means is that you can access a #temp table created
    in the parent process from the child of that parent, but not vice versa.
    Any dynamic SQL execution with either exec() or sp_executeSQL is ran in a child session/scope of the current one.Do 2 task:1 Rename your table to #Actives and Run your full script at time. 

    use northwind
exec('select top 5 orderId, customerId into #temp from orders; select * from #temp')
select * from #temp 
drop table #temp
    The #temp table gets created and queried once as seen in results panel.Then we have an error message in messages panel informing us:
    Invalid object name '#temp'.

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/


    • Edited by Ahsan Kabir Friday, January 18, 2013 11:01 PM
    • Marked As Answer by coolguy123SQL Tuesday, January 22, 2013 9:18 PM
    •