Saturday, February 2, 2019

No junctions with SQL Server FILESTREAM columns

We're using a SQL Server table has a FILESTREAM column, and we have some tests to verify reading & writing to this column. A little while back I started getting this error when I tried to run the tests that accessed this column:

System.ComponentModel.Win32Exception: The system cannot find the path specified.

I found a lot of information out there about this error: it can be caused by permission issues, by bugs in earlier version of SQL Server, and sometimes registry & setup errors. But none of the remedies they suggested worked: the SQL Server process had full control of all the directories involved, the registry hacks didn't change anything, and we're using 2017 Developer Edition. I thought it might be because I'd renamed by laptop (the test SQL Server instance is local), but after fixing the server name within SQL Server the error kept happening.

Then I remembered: our test database setup script has hard-coded full paths for the database files and I had set up a file junction on my laptop because I'd wanted it live in a different folder but didn't want to break compatibility. I wondered if that could be the culprit. I deleted the existing test database and re-created it in a different place altogether that didn't involve the junction. Sure enough, things started working! (I then re-wrote the setup script to use the default directories.)

I don't know if using junctions with FILESTREAM has a bug, is not supported, or I just didn't have all the permissions set up correctly, but either way the two things did not place nice together.

No comments:

Post a Comment