SQL SERVER – 2005 Enable CLR using T-SQL script

Before doing any .Net coding in SQL Server you must enable the CLR. In SQL Server 2005, the CLR is OFF by default.
This is done in an effort to limit security vulnerabilities. Following is the script which will enable CLR.

EXEC sp_CONFIGURE 'show advanced options' , '1';
GO
RECONFIGURE;
GO
EXEC sp_CONFIGURE 'clr enabled' , '1'
GO
RECONFIGURE;
GO

Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL

, , ,
Previous Post
SQL SERVER – UDF – User Defined Function to Find Weekdays Between Two Dates
Next Post
SQL SERVER – UDF – Function to Convert List to Table

Related Posts

3 Comments. Leave new

  • Brian Schumann
    July 16, 2009 6:44 pm

    Does this still require a restart of the service? I’d like to add this to a DDL script and would like to know if it would work without restarting the machine? Thanks for this information

    Reply
  • Imran Mohammed
    July 17, 2009 9:55 am

    Brain Schumann,

    In Older versions of SQL Server, yes this command needed a sql server instance restart. But From SQL Server 2005, if you use, RECONFIGURE WITH OVERRIDE, Restart is not needed.

    For Ex:

    EXEC sp_CONFIGURE ‘show advanced options’ , ‘1’;
    GO
    RECONFIGURE WITH OVERRIDE
    GO

    EXEC sp_CONFIGURE ‘clr enabled’ , ‘1’
    GO
    RECONFIGURE WITH OVERRIDE
    GO

    EXEC sp_CONFIGURE ‘show advanced options’ , ‘0’;
    GO
    RECONFIGURE WITH OVERRIDE
    GO

    Also, You can do the same thing through, Surface Area Configuration.

    Start – All Programs – Microsoft SQL Server 2005 – Configuration Tools – Surface Area Configuration – Surface area Configuration for features

    Click on the SQL Server instance, Expand Database Engine for that SQL Server instance and then point to CLR Integration on right side of panel, Check the radio button, Click Ok.

    You are done. This will also not require a restart of SQL Server Instance.

    Good if you test this out of your DEV/ TEST Env, before making change in production.

    ~ IM.

    Reply
  • Hi Pinal,
    Is the Exec with ‘advnced options’ mandatory???
    I am asking because i just ran :

    EXEC sp_CONFIGURE ‘clr enabled’ , ‘1’
    GO
    RECONFIGURE;
    GO

    and everthing is fine…

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version