error when running any query in sql server management studio: the file exists

Question

When I run any query in sql server management studio, I get the following error:

An error occurred while executing batch. Error message is: The file exists.

Restarting SSMS didn't help. Neither did rebooting the machine. The only thing I found on Google was someone saying "report the bug to microsoft" :P

(Windows XP Pro x64, SSMS 2005)

Solution 1

I've been struggling with this one for while and when I recently installed Toad for MySQL I got the same issue.

I installed sysInternals process monitor tool to try and work out which file was causing the issue.

The answer is temp files.

Both SQL server management studio and toad use a similar naming convention for their temp files. Both use the temp directory under your user account in Documents and Settings. In my case, there were over 60 thousand *.tmp files in that directory.

Watching the query execute through process monitor I could see the SQL IDE continually trying and failing to identify a temp file name which didn't exist until it finally gives up with a "The file exists" error.

The solution is simply to clear out the *.tmp files in your local settings temp directory.

Both SQL Management Studio and Toad for MySQL are now working fine on my machine.

Hope this helps.

Solution 2

Going further into Stephen's answer, the path would be:

• For Windows XP: C:\Documents and Settings\%USERNAME%\Local Settings\Temp
• For Vista and above: C:\Users\%USERNAME%\AppData\Local\Temp
• Or simply paste %TEMP% into the Windows Explorer address bar to get the path.

I recommend this little plugin in case you can't access the route:

Take Ownership
http://www.sevenforums.com/tutorials/1911-take-ownership-shortcut.html

Solution 3

I ran into the same issue with SQL Server 2012 running on Windows 8.1. As @Stephen mentioned, the issue is with the temp files but I couldn't find them in the location he mentioned. Solved the problem by running disk cleanup and directing it to delete Temporary Files.