Forum Discussion

DominilMs's avatar
DominilMs
Frequent Visitor
1 year ago
Solved

Temp table not working in Pipeline Script activities

Hello, I cannot find inforamtion why such code works DECLARE @Temp TABLE ( sCode VARCHAR(30))  INSERT INTO temp ( sCode ) VALUES ('1'); select top 1 * from @Temp and this one give me error that #te...
data engineering
data pipeline
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    1 year ago

    Hi DominilMs,

    Thank you for your follow-up.

    The problem you are facing happens because temporary tables (like #temp) in Microsoft Fabric are only available within the current session. Even though the operations are done in the same Script activity, Fabric may treat them as separate sessions. This causes the #temp table to disappear and gives the error "Invalid object name '#temp'."

    Please follow these steps to fix the issue:

    1. Use permanent tables instead of temporary ones. Permanent tables are saved in your Lakehouse or Data Warehouse and can be used across different activities. This way, you will not have session-related issues. Permanent tables stay available in all sessions and activities, so the tables won’t disappear.

    2. If you don’t want to use permanent tables and your work is in only one Script activity, use table variables (like temp). These variables work only during the execution of that activity.

    If you have any more questions, please feel free to ask the Microsoft Fabric community.

    Thank you.

tianj's avatar
tianj
Regular Visitor
1 year ago

Hi:

 

Since our organization has a lot of exisiting T-SQL queries for existing reports that our customers are running all the time, does Microsoft Fabric have a ETA date that they will actually fix this temp table issue or do we have to convert all of our queries to use the proposed solutions here?  Amazon RDS for SQL Server supports temporary tables: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.TempDB.html  and Google Cloud also supports temporary tables.  https://cloud.google.com/sql/docs/sqlserver/features 

I guess without any ETA of when this issue can be resolved in Fabric, I would have to think about other options like AWS and Google cloud as alternatives?  Since it would take a lot of time to make these changes. Thanks,