FileTable objects require the FILESTREAM database option DIRECTORY_NAME to be non-NULL

On SQL Server 2014, while creating FileTable using T-SQL Create Table command "FileTable objects require the FILESTREAM database option DIRECTORY_NAME to be non-NULL" error occured. I checked SQL Server database properties like FileGroups for FileStream and Data Files for FileStream data. Then I checked database options tab for FileStream directory name on SQL Server Management Studio database properties screen.

I executed following T-SQL Create Table commands in order to create a FileTable in current database in order to keep images in an advanced manner for my application.


CREATE TABLE ImagesTable AS FileTable

-- or

CREATE TABLE ImagesTable AS FileTable

WITH (

 FileTable_Directory = 'ImagesDirectory',

 FileTable_Collate_Filename = database_default

)

Code

Although I did not get the Default FileStream filegroup is not available in database error this time because I had previously created the FileStream Filegroup, this time I experienced following error message:

Msg 33414, Level 16, State 1, Line 4
FileTable objects require the FILESTREAM database option DIRECTORY_NAME to be non-NULL.
To create a FileTable in the database 'kodyaz.development', 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 'kodyaz.development' disable or drop the existing FileTables.

SQL Server create FileTable error due to FileStream Directory name

This is eventually an easier problem to solve. But first let's check the filegroups tab in database properties.

Open SQL Server Management Studio. Connect to related SQL Server instance.
On the Databases list in Object Explorer, right click on the database which you want to create filetable.
On the context menu, choose Properties
Navigate to Filegroups. You will see a filestream filegroup is created for your current database datafiles.

SQL Server FileStream Filegroups for FileTables
SQL Server 2014 Database filegroup for FileStream

If you switch to Files tab of the current SQL Server database properties screen, you will see that the database administrator or the SQL developer has already created a database file with FileStream Data file type on the Filestream filegroup.

SQL Server 2014 FileStream data file
SQL Server 2014 Database file for FileStream data to store images, or other binary files

Up to now, everything goes configured in the correct way. The error "FileTable objects require the FILESTREAM database option DIRECTORY_NAME to be non-NULL" is related for the following missing configuration.

Please switch to Options tab on the database properties screen.
Under FileStream configuration section, you will see FileStream Directory Name is left blank.

FileStream directory name configuration on SQL Server database
SQL Server 2014 Database Options for FileStream Directory Name

Type a name for the FileStream directory name on the database options screen. Please note that if you click OK to continue, SQL Server will require all open connections to the current database to be closed to apply the changes you made for the FileStream.

Database administrators can apply this change during a maintenance downtime or when the database connections are minimized.

It is possible to modify the database properties for filestream directory name and set the name of the FileStram directory using Alter Database T-SQL command as follows:


ALTER DATABASE [kodyaz] SET FILESTREAM( DIRECTORY_NAME = N'FileStreamDirectory' ) WITH NO_WAIT

Code

After database filestream directory definition is completed, SQL developer can successfully create filetables on the database by execution above Create Table ... as FileTable command in this SQL tutorial.

Again on the Database Properties screen Options tab, right after the FileStream Directory Name configuration, another database FileStream option exists: FileStream Non-Transacted Access
If SQL Server database administrator or SQL developer sets the value of FileStream Non-Transacted Access to Full, you will be able to see the FileTable contents using Windows Explorer.

If SQL Server database is configured with Full FileStream Non-Transacted Access, then users can simply insert images and binary files into SQL Server FileTable using copy-paste or drag-drop operations on Windows Explorer aka File Explorer on the FileTable directory.

access SQL Server FileTable using Windows Explorer

Here is the result set if I execute a SELECT query on the SQL FileTable

SQL Select query over SQL Server FileTable database table