how to Enable filestream in SQL Server 2008 R2 using C# code

Question

I have a setup file created for my wpf application which will install the .net framework 4 and sql server 2008R2 on a client machine.

However before running the sql script in my c# code I need to enable file stream in Microsoft SQL Sever 2008 R2/ Configuration tools on the my machine.

I need a way of doing it in c# code rather than doing it manually.

I have tried the following:

Enable FILESTREAM Feature Using Transact SQL (TSQL)

from page, http://www.mssqltips.com/tip.asp?tip=1838 but it didnt work.

Thanks and Best Regards!

"didn't work" - did you literally get no error messages, no diagnostic information, and FILESTREAM remained disabled, or did you get some information you can share with us? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 12, 2011 at 8:05
Hello, yes I didnt get any error message when I run USE master Go EXEC sp_configure 'show advanced options' GO EXEC sp_configure filestream_access_level, 1 GO RECONFIGURE WITH OVERRIDE GO but my file stream is still disabled — CodeMonkey
– CodeMonkey, Commented Jul 12, 2011 at 9:21

vladimir khozeyev · Accepted Answer · 2015-04-09 11:30:42Z

Call this function.

private void EnableFileStream(string instanceName)
{
    //sql 2008 - ComputerManagement10, sql2012 - ComputerManagement11
    string SqlWmiPath = @"\\.\root\Microsoft\SqlServer\ComputerManagement10"; 

    string WqlSqlFs = "SELECT * FROM FileStreamSettings WHERE InstanceName='" + instanceName + "'";

    ManagementScope scope = new ManagementScope(SqlWmiPath);
    int arch = Environment.Is64BitOperatingSystem ? 64 : 32;
    scope.Options.Context.Add("__ProviderArchitecture", arch);
    scope.Connect();

    WqlObjectQuery wql = new WqlObjectQuery(WqlSqlFs);
    ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, wql);
    ManagementObjectCollection moColl = searcher.Get();

    foreach (ManagementObject m in moColl)
    {
        ManagementBaseObject param = m.GetMethodParameters("EnableFileStream");
        //0 - off
        //1 - enable transact sql
        //2 - enable transact sql and stream acess
        //3 - enable transact sql and stream acess and remote stream access
        param["AccessLevel"] = 1; 

        param["ShareName"] = instanceName;
        var output = m.InvokeMethod("EnableFileStream", param, null);
        if (output != null)
        {
            uint retValue = (uint)output.GetPropertyValue("ReturnValue");

            //analyze return value
        }
    }
}

Restart system service corresponding to the instanceName. If instanceName is SQLEXPRESS, then service name will be MSSQL$SQLEXPRESS. Restarting can be done by system console, or by another way.

3.1 sc stop "MSSQL$SQLEXPRESS"

3.2 Wait until it is stopped (check service state: sc query "MSSQL$SQLEXPRESS")..

3.3 sc start "MSSQL$SQLEXPRESS"

Pero P. · Accepted Answer · 2011-07-12 08:27:18Z

0

Generally for any administration of SQL Server through .net code you should be looking at SQL Server Management Objects. It may support enabling FILESTREAM at the DB level, but I'm not sure.

If it's of any help you are however at least able to create a new FILESTREAM enabled FileGroup through the api. An example exists on that MSDN page, plus there are some blog samples out there if you google smo filestream.

edited Jul 12, 2011 at 8:27

answered Jul 12, 2011 at 7:44

Pero P.

27.1k9 gold badges65 silver badges87 bronze badges

Collectives™ on Stack Overflow

how to Enable filestream in SQL Server 2008 R2 using C# code

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related