SQL Server Portal

Recently, I was working on semantic search and tried to create a database having filestream and got an error FILESTREAM feature is disabled. After some research, I found the solution.

Let’s discuss this in detail:

Message Number: 5591

Severity : 16

Error Message: FILESTREAM feature is disabled.

Error Generation:

Given below is the script that I tried to execute but it resulted with the following error.

USE master
GO
CREATE DATABASE [SampleDB]
ON PRIMARY
( NAME = N'SampleDB',
FILENAME = N'C:\DATA\SampleDB.mdf'
),
FILEGROUP SampleFileGroup CONTAINS FILESTREAM
(NAME = SampleFileTableFS,
FILENAME='C:\Data\SampleFileTable'
)
LOG ON
( NAME = N'SampleDB_log',
FILENAME = N'C:\DATA\SampleDB_log.ldf'
)
WITH FILESTREAM
( NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'SampleFileTableDB'
)
GO
--OUTPUT

Msg 5591, Level 16, State 1, Line 1
FILESTREAM feature is disabled.

Ooopps…… I am unable to create a database having FILESTREAM. So what is the solution ?

Solution:
Let me explain the solution step by step.

Step 1 :
Click on the Start >>All Programs >>Microsoft SQL Server 2012 >>Configuration Tools >> SQL Server Configuration Manager, as shown in the image below.

Step 2 :
Once you click on SQL Server configuration manager, it will open SQL Server configuration manager options. It has two panes. Now you need to select SQL Server Services from the left hand pane and select the SQL Server Instance on which you need to enable the FILESTREAM. Just right click on it and select PROPERTIES as shown in the image below.

Step 3 :
Once you select the PROPERTIES, it will open SQL Server properties dialogue box. Now you need to find FILESTREAM Tab and select it.
Once you select it, you will find given below options:

1. Enable FILESTREAM for Transact-SQL access (You need to enable this to enable FILESTREAM).
2. Enable FILESTREAM for file I/O access. (If you need to read and write data (FILESTREAM) from windows then enable it and provide a windows share name)
3. Allow remote clients to have streaming access to FILESTREAM data. (If you need to give access to remote clients on FILESTREAM data then enable it)

And Click APPLY button as shown in the picture below.

Step 4 : 

Once you are done with SQL Server Configuration Manager, open SQL Server Management Studio and open a new query window.

Step 5 :

Now, you are in the new query window, just execute the given below script. And after that DO NOT forget to restart the SQL Server SERVICE.

EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO

Step 6 :
FILESTREAM is enabled now. You can execute any script having FILESTREAM. It will be executed successfully.

Read Full Post »