Refresh Failure Due to Temp Database Already Existing

[McGran]

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?

[Anandkkau]

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

[Lmendoza]

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 

[mmeyer26 · Accepted solution]

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]

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]

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

[mmeyer26]

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?

[lbendlin]

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. 

[aj1973]

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.