Filestreams are a great way to store files in SQL Server. SQL Server makes this super easy by managing the storage of the physical files on the file system. But what if you want to get access to these files or if you want to know where they are stored. SQL Server wants you to access these files through a network share managed by SQL Server. They provide a function called PathName that you can call to get this path. You will call this function like you would call an extension method in .Net. In the example below, we will assume that the column InvoiceFile is a Filestream datatype. We call the PathName() function to get the share name to where the files are stored.
SELECT TOP 1 InvoiceFile.PathName() AS DirectoryOfFile FROM Invoice
I get the following error when I try to run a query with the “PathName()” call in it: FILESTREAM feature doesn’t have file system access enabled.
Sounds like you need to enable it.
First launch your SQL Server Configuration Manager. Select SQL Server Services on the left. Then right click on the running instance of your SQL Server (should have a green arrow by the icon). Select properties. Go to the FileStream tab and select all the checkboxes.
Next go back to Management Studio and start a new query on your database. Run:
sp_configure ‘filestream_access_level’, 2
RECONFIGURE WITH OVERRIDE
This should do it, remember it won’t show the path until you put the initial row in the table.
FYI: You may need to restart the SQL Server instance.