SQL Server FileStream Errors and Configuration Steps for FileTable Creation

SQL Server FileStream feature and FileTable for storing binary files in SQL database which enables data consistency with database tables and binary image or files is one of the most enjoyed features of SQL Server for me. Since database developers and SQL Server database administrators have to configure some database options before creating a FileTable, I experienced a few time errors related with a missed step of FileStream configuration. So I believe it will be good to note how SQL Server FileStream can be enabled on a SQL Server instance and SQL database and how a FileTable can be used easily.

1) Check FileStream is enabled and configured at SQL Server instance level
2) Create FileStream file group for target SQL Server database
3) Create FileStream data file on target SQL database
4) Type a unique FileStream directory name on the SQL Server instance for target database
5) Configure FileStream non-transacted access level
6) Create FileTable

Before I tried to create File Table on SQL Server database, I created a FileStream File Group using the Database Properties dialog screen.

SQL Server database file groups to use FileStream data

Then I created a Filestream Data file using the Database Properties data Files tab

SQL Server data files for FileStream feature

And while I was creating a file table on the newly created SQL Server database (with FileStream file group and data file created) using below SQL code, I got following SQL Engine error.


CREATE TABLE [Exam].[ExamFiles] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [ExamData_FSFG]

WITH

(

 FILETABLE_DIRECTORY = N'ExamFiles', FILETABLE_COLLATE_FILENAME = Latin1_General_100_CI_AS

)

Code

And the error thrown by SQL Engine is as follows:

Msg 33414, Level 16, State 1, Line 13
FileTable objects require the FILESTREAM database option DIRECTORY_NAME to be non-NULL. To create a FileTable in the database 'KodyazWork', set the DIRECTORY_NAME option to a non-NULL value using ALTER DATABASE. Or, to set the DIRECTORY_NAME option to NULL, in the database 'KodyazWork' disable or drop the existing FileTables.

Then I remember that I had one missing database configuration that caused the error as the error message noted.
On Database Properties screen Options tab, there is a property to be asssigned.
That is FileStream Directory Name

After assigning a directory name for the main FileStream folder or directory for that database the CREATE TABLE as FILETABLE should work successfully.

SQL Server database Filestream directory name

Of course, you may be as unlucky as me so that you have the probability to use the same FileStream directory name that you have used for an other database.

TITLE: Microsoft SQL Server Management Studio

Alter failed for Database 'KodyazWork'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink? ProdName=Microsoft+SQL+Server&ProdVer=15.0.18040.0+((SSMS_Rel).180920-2155) &EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText &EvtID=Alter+Database&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

FILESTREAM DIRECTORY_NAME 'FileStreamDirectory' attempting to be set on database 'KodyazWork' is not unique in this SQL Server instance. Provide a unique value for the database option FILESTREAM DIRECTORY_NAME to enable non-transacted access.
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 33415)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.1000&EvtSrc=MSSQLServer&EvtID=33415&LinkId=20476

Another important configuration is the setting FileStream Non-Transacted Access. If you keep this FileStream property value as "Off" then you wont be able to see the FileStream Directory as a shared folder of the server which is running the SQL Server instance of the related database.

Setting the FileStream Non-Transacted Access property to Full, the FileStream Directory will available as a shared folder

SQL Server FileStream Non-Transacted Access settings

After I enable non-transacted access to filestream data files, the shared folder is available now.
The FileTables also have their own data folders and it is possible to copy and paste files from other directories to those shared FileTable folders.

SQL Server FileStream directories as shared folders

It is also an other alternative to insert a sample data into the File Table in binary format as follows by using SQL code


insert into [Exam].[ExamFiles] (

 [name], file_stream

) values (

 'Kodyaz', cast('kodyaz.com' as varbinary(max))

)

Code

After all troubles and SQL exceptions I have experienced, I am able to use SQL FileStream and FileTable features on my new SQL Server 2019 instance. I hope you will not experience those errors if you check and follow the proecss given in step by step action list to enable FileStream on SQL Server database.