deploy SQL Server Express LocalDB database app with "clr enabled"

Cross posted on MSDN

My app uses the group_concat custom functions on codeplex

I am getting the following error when starting up the app on target machine.

Execution of user code in the .NET framework is disabled. Enable clr enabled configuration option.

Assuming localdb is installed on target machine, what can I do to enable clr along with install? On my machine, i just opened SSMS and executed the script there.

sp_configure 'clr enabled', 1; GO RECONFIGURE; GO

However, SSMS will not be installed on most clients and I am not sure how to make it so the database can use these functions. Any suggestions?

EDIT: I have attempted setting this in c# from the app in two different ways, but both have failed.

 public void EnableCLR(string ConnectionString)
    {
        using(SqlConnection con = new SqlConnection(ConnectionString))
        {
            using(SqlCommand cmd = new SqlCommand("sp_configure 'clr enabled', 1", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                con.Open();

                cmd.ExecuteNonQuery(); //Error: Unable to find stored procedure "sp_configure 'clr enabled', 1"
            }
        }
    }

Then I tried this

 public void EnableCLR(string ConnectionString)
    {
        using(SqlConnection con = new SqlConnection(ConnectionString))
        {
            using(SqlCommand cmd = new SqlCommand("sp_configure", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                con.Open();

                SqlParameter clrParam = new SqlParameter();

                clrParam.ParameterName = "clr enabled";

                clrParam.Value = 1;

                cmd.Parameters.Add(clrParam);

                cmd.ExecuteNonQuery(); //Error: @clr enabled is not a parameter for procedure sp_configure.
            }
        }
    }

Answers


How are you installing LocalDB? I assume you are attaching the MDF file via the Connection String, hence unable to execute these statements in a SQL script.

Assuming there is also no SQLCMD.EXE to run a query with, you should be able to create a separate Console App that the installer can run after installing LocalDB. That Console App can run SQL statements via SqlCommand.ExecuteNonQuery(). Those SQL statements can either be hard-coded (harder to change) or it can get the configuration SQL from the app.exe.Config file.

Something along the lines of:

SqlConnection _Connection = new SqlConnection(@"(localdb)\InstanceName");
SqlCommand _Command = null;

try
{
  _Command = _Connection.CreateCommand();
  _Connection.Open();

  _Command.CommandText = "EXEC sp_configure 'clr enabled', 1;";
  _Command.ExecuteNonQuery();

  _Command.CommandText = "RECONFIGURE;";
  _Command.ExecuteNonQuery();
}
finally
{
  if (_Command != null)
  {
    _Command.Dispose();
  }
  _Connection.Dispose();
}

Need Your Help

How to add an ActionListener to an anonymous object?

java swing object actionlistener anonymous

How can i add ActionListeners to the MemoryFeld objects in the nested for-loop ?

--force-yes required to install heroku toolbelt?

ubuntu heroku heroku-toolbelt

I've installed heroku toolbelt on many different platforms to date, and was surprised to find myself having trouble doing it on ubuntu 12.04. Some of the issues I encountered are explained, I thin...