Sunday, June 26, 2011

RECONFIGURE WITH OVERRIDE

I was recently trying to implement a CLR function in one of my dbs. I deployed and was about to test when i got the error that CLR is not enabled in system and its required for running .NET code. I used sp_configure for enabling clr as follows

sp_configure 'clr enabled',1

and got message as below
Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.
While running RECONFIGURE I got below error

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

The reason was the run_value of configuration cant be changed without server stop and restart. The only option in that case is to use WITH OVERRIDE option. This wont force the config value checking.

More details can be seen in below MSDN link