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 (http://blog.SQLAuthority.com) , BOL

About these ads

3 thoughts on “SQL SERVER – 2005 Enable CLR using T-SQL script

  1. 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

    Like

  2. 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.

    Like

  3. 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…

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s