temp table getting dropped in synapse in the same session.

VS29 246 Reputation points
2020-12-19T19:28:59.903+00:00

Hi All,

I am trying to create temp tables for some analysis on the synapse analytics. While I am able to operate the data from temp table in the same execution but I am unable to access temp table individually in the same session. Just wondering if this is any kind of setting on the synapse?

fyi..I am using MFA authentication.

49938-2020-12-21-8-50-09.png

If I run Line 1 through Line 8 in the same execution...I am able to see data from #temp table. however, If I run line#8 individually after Line#1 though Line#6...I am getting the following error.

Msg 208, Level 16, State 0, Line 7
Invalid object name '#Temp'.

Please help. TIA

edit-added picture w/some more details

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,363 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,695 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
Accepted answer
  1. David Browne - msft 3,766 Reputation points
    2020-12-21T17:34:35.633+00:00

    This is a limitation of the SQL Scripts experience in Synapse Studio. It looks like each execution is run in a nested batch, so can't create a session-level temp table.

    Use SSMS instead and this works fine.

    I've added a feedback item on Synapse Studio, but you should do that too to help get this prioritized.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ARR 41 Reputation points
    2020-12-20T23:51:12.957+00:00

    Any #temp table created will be visible or can be accessed, only the session query window it was created in.

    If you have another query window open (query window other than the one where you created the #temp table) , where you are trying to execute say, select * from #temp - it will not find the #temp table.

    0 comments
  2. Vaibhav Chaudhari 38,576 Reputation points
    2020-12-21T08:22:44.723+00:00

    I don't see any limitations in Synapse as such. Maybe new sessions is getting generated if you are trying to access temp table via ADF or some other service

    https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-temporary

    ----------

    Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

    0 comments
  3. VS29 246 Reputation points
    2020-12-21T19:26:13.95+00:00

    It's working as intended after installing the latest version(v18.8).

    but, just wondering why it didn't retain data in the first place on the earlier version(v17.9).

    Thank you All!