Enabling File-Stream in "Katmai"-SQLServer 2008

If you are working with SQLServer 2008 "Katmai" and want to import any database then the most common problem that you all can face is that the system will ask to enable file stream.
The error message will be :-

System.Data.SqlClient.SqlError: FILESTREAM feature is disabled. (Microsoft.SqlServer.Smo)

Now if you will try to enable your this option using SQL services then you will find that you are not that it is not working.
So here is a simple solution for us.
Just run a simple command and enable your "File Stream" option in "Katmai".
Command is :-

EXEC sp_filestream_configure @enable_level = 3;

The syntax of this command is :-

sp_filestream_configure [ [ @enable_level = ] level ] [ [ , @share_name = ] 'share_name' ] ;
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->

Arguments
@enable_level = level
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->

• Specifies the access level of FILESTREAM storage that you are enabling on the instance of SQL Server. level is int, and can be one of the following values:

0	Disabled. This is the default value.
1	Enabled only for Transact-SQL access.
2	Enabled only for Transact-SQL and local file system access.
3	Enabled for Transact-SQL, local file system access, and remote file system access.

·

@share_name = 'share_name'

Specifies the file share name that is used to access FILESTREAM values through the file system. share_name is a sysname. You can set the share_name value when you change the enabled state from 0 (disabled) or 1 (Transact-SQL only) to file system access (2 or 3).

If you want to change the share_name value when the enable level is 2 or 3, you must first set the enable level to 0 or 1. You can then change the share_name value when you re-enable file system access.

If this parameter is not specified, the instance name of SQL Server is used as a default value.

Return Code Values
0 (success) or 1 (failure)

Result Sets
None

Remarks
When you run sp_filestream_configure, you might have to restart the instance of SQL Server. A computer restart is required when you enable FILESTREAM storage for the first time, or after you install any update to the RsFx driver. An instance restart is required when you disable FILESTREAM storage from an enabled state. To restart the instance of SQL Server, run the RECONFIGURE statement after you run sp_filestream_configure. To see the current FILESTREAM configuration, run the following query:

SELECT SERVERPROPERTY ('FilestreamShareName') ,SERVERPROPERTY ('FilestreamConfiguredLevel') ,SERVERPROPERTY ('FilestreamEffectiveLevel');

FilestreamConfiguredLevel shows the currently configured level of FILESTREAM access, and FilestreamEffectiveLevel shows the effective value of FILESTREAM access. These values might be different if either an instance restart or a computer restart is pending.

When FilestreamEffectiveLevel differs from FilestreamConfiguredLevel and you run sp_filestream_configure without parameters, the results contain an informational message that describes why the values are different.

Permissions
Requires membership in the sysadmin fixed server role and the Windows Administrators group on the local computer.

Examples
The following example shows an instance of SQL Server (MyFilestreamSqlServerInstance) that is being enabled for remote file system access.

EXEC sp_filestream_configure @enable_level = 3, @share_name = "MyFilestreamSqlServerInstance"; RECONFIGURE
GO

Have a nice day :-)

My pic

This is me pic in Persistent.