none
CLR Enabled

    Question

  • Can anyone tell me what this means and how to fix it? I created a stored procedure in VS2005 and did a build. When I went to SQL Server there was the stored procedure but when I run it I get the error....

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

    I changed the 'clr enabled' property to 1 using sp_configure but I still get this error.

    Thanks
    Mike

    Friday, November 04, 2005 10:34 PM

Answers

  • Hi.

    You need to run RECONFIGURE.

    sp_configure 'something', value
    go
    RECONFIGURE
    go
    sp_configure 'something'
    go
    Friday, November 04, 2005 11:20 PM
  • There are two parts to a CLR Function.  There is the assembly, and then there is the Function object in SQL itself.  Look under Progamability => Assemblies and see if an Assembly is listed there.  Then you can right click on it and look at the dependencies.  If the function is listed there, then you need to enable CLR to use the function.  You can deploy an assembly and create the function object, without CLR Enabled.  You can't use it until you enable CLR.

     

    Saturday, April 19, 2008 12:47 AM
    Moderator

All replies

  • Hi.

    You need to run RECONFIGURE.

    sp_configure 'something', value
    go
    RECONFIGURE
    go
    sp_configure 'something'
    go
    Friday, November 04, 2005 11:20 PM
  • Thank you again Gorm!
    Friday, November 04, 2005 11:31 PM
  •  Gorm Braarvig wrote:
    Hi.

    You need to run RECONFIGURE.

    sp_configure 'something', value
    go
    RECONFIGURE
    go
    sp_configure 'something'
    go


    Hi!

    I'm having the same problem. I'm just wondrin what that 'something' is. Is that a placeholder of some sort? What can be the values for that?I really need a working command using that 'something'. Thanks.
    Tuesday, November 22, 2005 5:14 AM
  • That 'something' is the value you want to change through sp_configure. In the case of enabling CLR, it is 'clr enabled'. The full code for this is:

    sp_configure 'clr enabled', 1
    go
    reconfigure
    go

    The reconfigure is important, and it has to be done in a separate batch, therefore the go between the sp_configure and reconfigure.

    Also, you can change this as well (together with other stuff) through the SQL Server Surface Area Consiguration tool (SAC). Go to Start | SQL Server | Configuration Tools and you'll find it. (The names are from memory and not correct - but you'll know what I mean).

    Niels
    • Proposed as answer by Scott Herbert Wednesday, April 20, 2011 6:04 AM
    Tuesday, November 22, 2005 9:23 AM
    Moderator
  • sp_configure 'something', value
    go
    RECONFIGURE
    go
    sp_configure 'something'
    go

    'something' is a placeholder, yes. To see all possible configurable options you might try

    -- sp_configure test

    sp_configure 'show advanced options', 1

    go

    RECONFIGURE

    go

    sp_configure

    go

    -- EO sp_configure test

    This should list everything you can replace 'something' with.

    I guess that most of what you are supposed to turn on or off is available in GUIs too. I usually guess wrong, though.


    Hope this helps.

    • Proposed as answer by GregBrus Tuesday, November 05, 2013 4:41 PM
    Tuesday, November 22, 2005 9:48 AM
  • I've done everything you suggested guys. I even verified it in the Surface Area Configuration. Still no luck.

    The sproc works using sqlcmd. Is this a normal behavior?
    Tuesday, November 22, 2005 11:12 AM
  •  vanni wrote:
    I've done everything you suggested guys. I even verified it in the Surface Area Configuration. Still no luck.

    The sproc works using sqlcmd. Is this a normal behavior?

    Hmm, are you saying that you can execute the SQLCLR proc from sqlcmd but not from somewhere else? If that's the case; where can you not execute the proc from, and what is the error. If the error is that the CLR is not enabled I'd say that you are not executing against the same server instance as you do when executing using sqlcmd.

    Niels
    Tuesday, November 22, 2005 6:02 PM
    Moderator
  • I only have 1 instance of SQLEXPRESS, with instance name 'SQLExpress'.Sad
    Wednesday, November 23, 2005 10:57 AM
  • Are you perhaps running a user instance as well?  If so, you'll need to enable CLR integration in the user instance separately from the main instance.

    Wednesday, November 23, 2005 1:12 PM
  • Hi Nicole! This is something new. How should I know that I'm using a User's intance? Btw, the db i'm using is local, found within the project's folder. Does this have any bearing on my problem?

    Wednesday, November 23, 2005 3:59 PM
  • If your connection string contains "User Instance = true", then you're using a user instance.
    Wednesday, November 23, 2005 4:14 PM
  • I'm indeed using user instance. I only enabled CLR integration on the main instance. My problem is how to enable it in the user instance. Is there a way to do so on the fly in my C# app?

    Thursday, December 01, 2005 1:55 AM
  • Just issue the same series of T-SQL statements (already covered earlier in this thread) that you would use to enable CLR use in a standard instance.  Using ADO.NET, this can be accomplished by setting the CommandText property of a SqlCommand instance to the desired T-SQL statement, then calling the ExecuteNonQuery method of the SqlCommand.

    Thursday, December 01, 2005 12:39 PM
  • This works very well!

    But if I change the .Net application, adds new methods etc. how do I tell the MS SQL Server about the new features, without dropping and adding the assembly?

     

    Tuesday, April 04, 2006 5:55 PM
  • Hi,

    You may use the ALTER ASSEMBLY command.

    Monday, April 10, 2006 3:48 AM
  • I have found a way to do this using SQLExpress 2005 and working in a "SQLServer" type of project in VB 2005.

    This is just a sample project that I found somewhere so I could start doing stored procedures in VB2005.

    Here's the code in the VB code file:

    Imports System

    Imports System.Data

    Imports System.Data.SqlClient

    Imports System.Data.SqlTypes

    Imports Microsoft.SqlServer.Server

     

    Partial Public Class StoredProcedures

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub WhateverStoredProcedure ()

    Dim Cmd As SqlCommand = New SqlCommand

    Cmd.CommandText = "SELECT * FROM MyWhateverTable"

    Cmd.CommandType = CommandType.Text

    Dim P As SqlPipe = SqlContext.Pipe

    P.ExecuteAndSend(Cmd)

    End Sub

    End Class

     

    Next you Build, then right-click the project file in the Solution Explorer and choose "Deploy".

    Now, in the "Test.sql" file that gets generated, put this text at the bottom, and note that there are no "GO"s here:

    EXEC sp_configure 'clr enabled' , 1

    RECONFIGURE

    EXEC dbo.WhateverStoredProcedure

    Monday, April 24, 2006 2:31 PM
  • Thank you Nicole!

    I'd been struggling with this for days, and now it's all solved with your tip.

    Monday, February 19, 2007 9:06 PM
  •    Hi All !

        Recenlty i saw in one blog of "Rodney Vinyard"  how to enable CLR funcationality in SQL Server 2005.

       

      http://geekswithblogs.net/vrod/archive/2006/07/17/85471.aspx

        In case that blog is moved so just copied the same process below.

     

       1. Explore "SQL Server 2005/Configuration Tools/Surface Area Configuration" in your Start menu.
         2. Select "Surface Area Configuration for Features"
         3. You will find "CLR Integration" option, activate it and save.

     

        So u needn't do the enabling through ur code.

        But i am yet to find the pros and cons of two method!!

       regards,

        Saroj

    Tuesday, July 17, 2007 4:48 AM
  • Naomi,

     

    Can you either open a new post and provide all of the details of your problem there, or provide specifically, what part of this post you are questioning?  If you have not enabled CLR following one of the methods on this post already, then you won't be able to use a CLR UDF.

     

    Wednesday, April 16, 2008 8:19 PM
    Moderator
  • There are two parts to a CLR Function.  There is the assembly, and then there is the Function object in SQL itself.  Look under Progamability => Assemblies and see if an Assembly is listed there.  Then you can right click on it and look at the dependencies.  If the function is listed there, then you need to enable CLR to use the function.  You can deploy an assembly and create the function object, without CLR Enabled.  You can't use it until you enable CLR.

     

    Saturday, April 19, 2008 12:47 AM
    Moderator
  • Thanks a lot. I found that there are quite a few CLR functions. Here is one

    USE [FCCMS]

    GO

    /****** Object: UserDefinedFunction [dbo].[FuncHebdateFromEngDate] Script Date: 04/29/2008 13:23:09 ******/

    ALTER FUNCTION [dbo].[FuncHebdateFromEngDate](@EngDate [datetime])

    RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER

    AS

    EXTERNAL NAME [SqlServerProject1].[UserDefinedFunctions].[FuncHebdateFromEngDate]

     

    Tuesday, April 29, 2008 5:25 PM
  • That worked!!!!!
    Monday, October 31, 2011 5:32 AM