Sunday, November 27, 2011
This example describes how to enable FILESTREAM feature in SQL Server 2008. By default, FILESTREAM is disabled in SQL SERVER 2008. Before going to start file streaming you must have to configure settings in SQL Server database engine.
Following error message will display when you will try to create file stream data type in your table.
Msg 1969, Level 16, State 1, Line 1
Default FILESTREAM filegroup is not available in database 'DATABASE NAME'.
Following settings will apply to enable FILESTREAM in SQL Server Database engine.
Step1: From Start menu, click on All Programs, click to Microsoft SQL Server 2008, click to Configuration Tools and click to SQL Server Configuration Manager as shown.
Step2: SQL Server Configuration Manager Window will open. Now, Click on SQL Server Services, select your SQL Server and go to properties from right click on instance of SQL Server as shown.
Step3: SQL Server Properties dialog box will open, click the FILESTREAM tab. Check the checkbox to enable FILESTREAM for Transact-SQL and click Apply button as shown.
- Enable FILESTREAM for transact-sql access: FILESTREAM option will enable after checking this check box.
- Enable FILESTREAM for File I/O streaming access: You can access FILESTREAM data from Windows by checking this checkbox.
- All remote clients to have streaming access to file stream data: Remote client will access the FILESTREAM data by checking this check box.
After applying all above steps, open your Microsoft SQL Server Management Studio and execute the following T-SQL in the query editor as given below.
EXEC SP_CONFIGURE FILESTREAM_ACCESS_LEVEL, 2
Note: Following are the FILESTREAM access level options.
- 0 – Disables FILESTREAM functionality.
- 1 – Enables FILESTREAM for Transact-SQL access.
- 2 – Enables FILESTREAM for Transact-SQL and Win32 streaming access.
- 3 – Enables FILESTREAM for Transact-SQL to allow remote client acces