CLR Enabled

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

Hi.

You need to run RECONFIGURE.

sp_configure 'something', value
go
RECONFIGURE
go
sp_configure 'something'
go|||Thank you again Gorm!|||

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

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.

|||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?|||

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
|||I only have 1 instance of SQLEXPRESS, with instance name 'SQLExpress'.|||

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.

|||

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?

|||If your connection string contains "User Instance = true", then you're using a user instance.|||

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?

|||

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.

|||

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?