[9.5.452 Pro] - Sqlassist.sas cannot be written

[9.5.452 Pro] - Sqlassist.sas cannot be written

Another weird issue. I created a new snippet, clicked OK, but when I tried to invoke it in the editor it wasn't there. I checked again in Options > Code Snippets if I accidentally mistyped its name upon creation but it wasn't there either. Then I tried to recreate it several times without any success. It seemed as if the file Sqlassist.sas was read-only, albeit SA didn't complain about being unable to write it when closing the Options window. When I checked in Process Explorer if there was another process laying its dirty hands on it I was quite surprised to see that System (PID: 4) was the one keeping it held. Only after a reboot was it willing to release its iron grip. This is the second time I ran into this, and I must say, it's quite alarming.

Any hints about why on earth would System even need to access that file at all? Has anyone else experienced this?

Do you have UAC enabled?

With UAC enabled certain files and directories become virtualized. This may manifest in unusual ways. Different applications can see different things in the "same" files, but files aren't really the same. Case in point, if you start SA system tray application in Run as Administrator mode, and then start SSMS or SASE as a regular user, not from the system tray menu, or visa versa, they would run under different user accounts (same name different session privileges and everything else). The applications can refer to the same files in the same location, they think, but indeed all their I/O operations can get redirected to different physical files, and so the applications won't see changes made by others. There are no errors. How does it apply to SA? In the described scenario, if you make changes using the system tray application, and then look for them in SSMS, you may not always find them there. Same may happen with RDP sessions and local sessions.

I don't know if that is your case. Just throwing an idea for where you may want to check for the cause of the issue.

Thanks for the hint. I checked it, alas, to no avail. I'm completely baffled by this one.

I have UAC enabled, but I've always had. I'm not doing anything differently than in the last x years. According to SysInternals autoruns, there's an SQL Assistant.lnk in C:\Users\gemis\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup, which is put there by SA during install, I guess. I checked that file and it has its Run this program as an administrator setting disabled, meaning that SA is not started as an elevated process. I also start SE as a regular user (actually, I'm an admin on this machine, but I'm not starting it as one, so that shouldn't count).

After a reboot, I start SE as I usually do and Process Explorer shows no open handles on Sqlassist.sas. I create/modify snippets, formatting rules, and other settings, edit code in the editor, productivity statistics are getting updated, everything works normally until suddenly it doesn't.

The problem here is not that changes made by one app are not visible to others. It's the same application not seeing the same things in the same file. The changes aren't applied at all. None of them. Not even the statistics are updated anymore when editing code. The new snippets created (or the modifications to existing ones) in SA Options are not only not seen by SE, they are actually not written to the file. Clicking OK closes the SA Options window and after reopening it even SA Options show that there were no changes made.

And when I check in Process Explorer, Sqlassist.sas has an open handle in the System process (PID 4) that I can't even force to be closed. Only a reboot seems to able to remove that handle. It's annoying as hell. Mostly because I cannot pinpoint when/why it happens. If that file becomes virtualized for SA, it is done so that it reads from and writes to different files.

In theory there might be multiple SqlAssist.sas files created by Windows in different places. Different instances for different user accounts and sessions.

Since you already went as far as using Process Explorer perhaps you may want to try the following too:
1. Using Process Monitor (another great tool developed by SysInternals) monitor process I/O operations to see where changes are saved and where other processes look for them.
2. Or just turn off UNC for a little while and see if the issue is reproducible.

Okay, I've got it. Though I've got no idea how to fix that. The issue is caused by the Windows 10 native application of Spotify. When it is started it reads a copy(?) of SqlAssist.sas in the folder

	Quote:
	"C:\Users\gemis\AppData\Local\Packages\SpotifyAB.SpotifyMusic_zpdnekdrzrea0\LocalCache\Roaming\SQL Assistant\9.5"

I checked immediately after that and system (PID 4) already had a lock on the SqlAssist.sas in its original location

	Quote:
	C:\Users\gemis\AppData\Roaming\SQL Assistant\9.5

I guess that's the result of re-routing the file. I had to repeat the procedure several times to check this for I couldn't believe my own eyes!!! But it is true! As soon as Spotify is started, SqlAssist is screwed! And what's even more annoying is that closing/killing Spotify does not solve the issue, PID 4 keeps that file locked until a reboot/logout+login.

As for why that ug@&# mo!@#$%^& wants that file it has absolutely no business with remains a mystery. What's certain is that no more listening to meditative music during work. WTF???

Something is weird here. Why would "\LocalCache\Roaming\SQL Assistant\9.5" appear under "\Local\Packages\SpotifyAB.SpotifyMusic_zpdnekdrzrea0"

Anyway, there might be a simple solution for that issue. Use a different account to start Spotify. It won't have access to C:\Users\gemis\AppData

	SysOp wrote:
	Something is weird here. Why would "\LocalCache\Roaming\SQL Assistant\9.5" appear under "\Local\Packages\SpotifyAB.SpotifyMusic_zpdnekdrzrea0"

That is a very good question. ProcMon also reported processes from a couple of other (random and completely unrelated) apps reading the SqlAssist.sas file upon their startup. I'm curious why do they need to read that file? There's nothing inside it for them.

	SysOp wrote:
	Anyway, there might be a simple solution for that issue. Use a different account to start Spotify. It won't have access to C:\Users\gemis\AppData

Possibly. But that would be a workaround rather than a solution. There's something fishy here and I'd like to see it smashed instead of trying to hide the problem. I'm continuing my search, will report if I find anything. I'll remove Spotify from my "toolbox" for the time being.

What if you put spotify in the ignored targets? I had to put one of my PDF Editors in there otherwise SQL Assistant made it really cranky.

Just for giggles I took my ignore out and look what procmon spits out when filtering for stuff that read in the sqlassist.sas:

Also Nitro_Slider.exe (whatever that is) also reads it in.

Another tool Database.NET I sometimes use also reads the file. What's even more odd about this one is it's an app that's not even compatible with SQL Asisstant. I can't do Ctrl-Shift-F5 on it to set it as a target, it says "Unsupported window class"

And msexcel.exe and msword.exe read it as well. I suspect it's something SQL Assistant uses to check for compatible targets is what's "actually" doing it?

Thank you Mindflux. Adding Spotify executable file to ignored targets list is a very good thing to try. Don't know if it can help, but that would remove one potential dependency.

Hmm, this seems to be working. Thank you very much, Mindflux.

	gemisigo wrote:
	Hmm, this seems to be working. Thank you very much, Mindflux.

I hope it works. Doing dev work without music is painful.

I hit the same problem in recent version of SA.
And Spotify was a proper point.
Interesting is, that removing SA from auto-start and running it manually also helps.
So we have 2 options: removing either spotify or SA from autostart ;)
But yeah.. it's very interesting why it happens.

	michalk wrote:
	I hit the same problem in recent version of SA. And Spotify was a proper point. Interesting is, that removing SA from auto-start and running it manually also helps. So we have 2 options: removing either spotify or SA from autostart ;) But yeah.. it's very interesting why it happens.

Or just set Spotify in the ignored target list.

Yep.. I'm just trying to give as more info as possible.
Right now I'm experiencing SA being not able to save config.
I closed the SA but the file remains locked.

While it worked a few hours ago, I started Spotify manually (before adding the process to exception list) and then closed it after a while.

Resource monitor (resmon.exe) - even when run as Administrator - is not able to show the process which is locking the file.
On the other hand Process Explorer (running as administrator) shows that the file is locked by process "System".