Forum Discussion

McGran's avatar
McGran
Advocate II
2 years ago
Solved

Refresh Failure Due to Temp Database Already Existing

Hi there,

 

I have a report that was written a couple of years ago and has been merrily refreshing away without trouble until the last week or so (earliest failure I can see in refresh history is 29/01/2024).

 

The error indicates a data source error due to a temp table already existing.

As can be seen below, the failures are not consistent but, when they do occur, it seems that the first refresh of the day is successful while subsequent refreshes fail.

As such, it seems like the temp table from the earlier refresh still exists when the next refresh runs.

 

Other colleagues in the business have reported the same errors being returned on their reports and agree that this is a recent occurence affecting reports that have previously worked for months or longer.

 

Has anyone come up against anything similar, or has any ideas where I can point our Dev Ops team to investigate?

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    We added code to drop the temp table at the start of each source query that we had used temp tables in.  

     

    IF OBJECT_ID(N'tempdb..#tTempTableName') IS NOT NULL
    BEGIN
    DROP TABLE #tTempTableName
    END

     

    Another solution one of my team mates found was to convert their temp tables to ctes.  

     

    This is a work around we implemented but does not adress the core issue.

8 Replies

  • Anandkkau's avatar
    Anandkkau
    New Member
    1 year ago

    Hi ,

    I had the same issue. I just cleared the cache and it helped me to refresh successfully.

    file>options>data load>clear cache

     

    Thank you

  • aj1973's avatar
    aj1973
    Community Champion
    2 years ago

    Hi McGran 

    Most of the time Power Query can identify where the issue is when refreshing. So Open your semantic model in PQ and start investigating.

     

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Pray to your $deity that the Power Query was not doing some funky attempts at data write back, or calling a SP or something like that. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    My team started getting these errors from several dashboards at the end of July (2024).  No one has been able to give us a explanation on why this is suddenly happening. Some of the dashboards haven't been touched or thier schedules changed since 2022.  Was there any response from Microsoft on this issue?

  • Lmendoza's avatar
    Lmendoza
    Regular Visitor
    2 years ago

    Hi McGran 

    I have the same problem with some of my reports,  I don't know how to solve it,  

    I have  tried restaring the Gateway,  changing the privacy level and changing the  name of the Temporary tables  
    but the problem persist, 

    Do you solved this problem?

    I hope  that you can help me

    Daniel 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      We added code to drop the temp table at the start of each source query that we had used temp tables in.  

       

      IF OBJECT_ID(N'tempdb..#tTempTableName') IS NOT NULL
      BEGIN
      DROP TABLE #tTempTableName
      END

       

      Another solution one of my team mates found was to convert their temp tables to ctes.  

       

      This is a work around we implemented but does not adress the core issue.

      • blodus's avatar
        blodus
        Advocate IV
        1 year ago

        Unfortunately, dumass Microsoft marks this manual workaround as the solution to their bug and will now never fix it. 😞 

         

        And just use DROP TABLE IF EXISITS #tTempTableName