Post reply

#temp Table

  • July 4, 2017 at 3:17 pm

    #327035

    Hi

    I have a Stored Procedure that uses #temp tables. it creates them and drops them within the SP.   When I run this program from my Office network ... this SP completes successfully and next, I re-run the same SP by remotely connecting from home, I get the message that #temp table is either not present or I do not have permissions to drop it.

    As I mentioned, the SP run from office network completed successfully - so there's no hanging status. 

    1) What is the Life and Scope of a #temp table?   Is it per-session?  If so, is the same session being shared between the networks?
    2) How do I gracefully address this issue and be able to run this SP between both networks.

    thank you

  • July 4, 2017 at 3:38 pm

    #1949374

    etl2016 - Tuesday, July 4, 2017 3:17 PM

    1) What is the Life and Scope of a #temp table?   Is it per-session?  If so, is the same session being shared between the networks?

    Per session, and if created within a procedure, until that procedure ends.
    A session is a connection, so each connection to SQL Server is a separate session.

    Can you post the procedure? Or a mock procedure that has exactly the same problem?

    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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply