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 2...
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.

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

    • McGran's avatar
      McGran
      Advocate II
      1 year ago

      I like the CTE 'solution'.  That'll do for me.