Re: sp_configure (RECONFIGURE)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Sounds like you have the option "allow updates" enabled and are on SQL 2005.
This option has no effect in 2005 and causes these errors when you don't use
"with override". To get rid of the error run the following

exec sp_configure 'allow updates',0
reconfigure with override
go

You should now be able to use reconfigure without encountering errors.

--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
http://sqlblogcasts.com/blogs/sqldbatips


"Alex" <test!@xxxxxxxxxxx> wrote in message
news:uu41x76lHHA.1340@xxxxxxxxxxxxxxxxxxxxxxx
Hello
When I run (copied from BOL as an example of enabling AWE) the following
statement I got an error.It did changes but with error message. Should I
use WITH OVERRIDE?
Can someone explain on subject? Is it BOL incorrect?


sp_configure 'show advanced options',0
RECONFIGURE
GO
sp_configure 'remote login timeout', 21
RECONFIGURE
GO


DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Configuration option 'show advanced options' changed from 0 to 0. Run the
RECONFIGURE statement to install.
Server: Msg 5808, Level 16, State 1, Line 2
Ad hoc updates to system catalogs not recommended. Use the RECONFIGURE
WITH OVERRIDE statement to force this configuration.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Configuration option 'remote login timeout (s)' changed from 20 to 20. Run
the RECONFIGURE statement to install.
Server: Msg 5808, Level 16, State 1, Line 2
Ad hoc updates to system catalogs not recommended. Use the RECONFIGURE
WITH OVERRIDE statement to force this configuration.



.

Relevant Pages

  • Re: Identity columns
    ... reconfigure with override ... sp_configure 'allow updates', 0 ... this will fix your identity keys on all of your tables for the not for ...
    (microsoft.public.sqlserver.replication)
  • Re: Database : I/O error (torn page) detected
    ... Status still suspect. ... >> RECONFIGURE WITH OVERRIDE ... >> It won't work without allowing updates. ...
    (microsoft.public.sqlserver.server)
  • Re: Recover SQL Database from suspect status
    ... Sp_configure "allow updates", 1 ... Reconfigure with override ... > must reconfigure SQL Server to allow this. ...
    (microsoft.public.sqlserver.server)
  • Re: Replication errors - vicious circle
    ... Hilary Cotter ... >I receive the error "ad hoc updates to system catalogues are not allowed" ... >> reconfigure with override ...
    (microsoft.public.sqlserver.replication)
  • Re: Restoring from MDF and LDF
    ... reconfigure with override ... where name = 'BADDBNAME' ... Start database in emergency mode ...
    (microsoft.public.sqlserver.server)