CLR Integration in SQL Server (Problem in "sp_configure 'clr enabled', 1")

Question

 

HIiiiiiii all

I have installed both sql server 2000 and sql server 2005 Developer Edition in my system as well as .net 2003 and 2005. Now i want to enable clr integration in sql server 2005. so when i write

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

In Sql Statement then it gives me error
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'clr enabled' does not exist, or it may be an advanced option.
 
Valid configuration options are:

I have created a database user which have all the rights and roles. and while i check the stored system procedure sp_configure it is affacting mainly two system tables in the master tables

  master.dbo.spt_values
  master.dbo.sysconfigures
and this table does not contains field like ''clr enabled'' so tht we can change the state

so plz plz help me to enable clr in the sql server 2005.
I need it so muchhh...

Thanking you in advance

Answer 1

Try enabling the 'advanced options' before you enable clr

sp_configure 'show advanced options', 1

 

OR

 

Use the surface area configuration manager to 'Enable CLR Integration'

Programs->Microsoft SQL Server ->Config tools->Surface Area Config Mgr.

Answer 2

Hiiiii

I have also tried the enabling advance option using this script

 

EXEC sp_configure 'show advanced options', 1 -- make them available

reconfigure

EXEC sp_configure 'clr enabled', 1 -- turn on OLE

reconfigure

EXEC sp_configure -- to see the new value

EXEC sp_configure 'show advanced options', 0 -- make them unavailable

reconfigure

 

but this script also gives me error like this

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78

The configuration option 'clr enabled' does not exist, or it may be an advanced option.

Valid configuration options are:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

 

and i dont know much about surface area config mgr but i think it is for remote database or wht

 

Plz help me i need this urgently and

thnxxxxx again.......

Answer 3

Surface Area Config Mgr. is the easiest way to do it.

 

Open up Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration.

 

Click on the 'Surface Area Configurations for Features' link at the bottom of the dialog to bring up next dialog.

 

In this second dialog, in the left pane select 'View by instance' tab. It shows you a tree view, something like

 

- MSSQLSERVER

- Database Engine

Ad Hoc Remote Queries

CLR Integration

DAC

...

 

Choose MSSQLSERVER/Database Engine/CLR Integration

Check the 'Enable CLR Integration' in the right pane and say 'OK'.

 

Hopefully this should set you up.

 

 

Answer 4

Hiiiiiiiii

 

I have set up this sucessfully

 

Really this is easyest way to do this

 

thnxxxxxxx a lot

 

and how do i check tht my clr confitration is enabled or not

because when i write query like this

 

exec sp_configure 'show advance options'

 

then this again executes with the previous err....

 

thnx a lot

Answer 5

select [value_in_use] from sys.configurations where name = 'clr enabled'

Answer 6

Hi,

 

I don't see CLR Integration and all that you have listed above.  I did an upgrade from SQL 2000 to 2005.  Have I missed out something during the installation?

 

Thanks in advance,

APUK

Answer 7

Hi,

 

I re-installed SQL 2005 with upgrade option and got those options.

 

Thanks,

APUK