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
CREATE TABLE ImagesTable AS FileTable
FileTable_Directory = 'ImagesDirectory',
FileTable_Collate_Filename = database_default
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.
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 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 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.
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
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.
Here is the result set if I execute a SELECT query on the SQL FileTable