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.