Error message: Alter failed for Server ‘SivaSQL2008’
When I ran SQL Profiler and checked why its failing, observed;
Errors when it was trying to execute
SP_CONFIGURE 'USER OPTION',0 ;
RECONFIGURE
Error: 5808, Severity: 16, State: 1
Ad hoc update to system catalogs is not supported.
The reason for why AD HOC UPDATE TO SYSTEM CATALOGS is not supported.
Why it is trying to change SYSTEM CATALOGS at first place?
In SQL Server 2000 and older, this option allowed users to make updates directly to the system tables.
In SQL Server 2005 and higher, the system tables are, replaced with the Resource Database and system views.
This option is no longer supported in SQL Server 2005 and higher version, and though we can set Allow Updates to 1 with no error. As soon as we run RECONFIGURE, we will receive the error that ad hoc updates are not supported.
Because Allow Updates had been set to 1 and my Maintenance Plan runs the RECONGIFURE statement, this error was thrown and the Maintenance Plan failed.
As per Books On Line:
Changing the allow updates option will cause the RECONFIGURE statement to fail. Changes to the allow updates option should be removed from all scripts.
Resolution:
So to resolve is configured ALLOW UPDATE back to 0 and again ran Maintenance Plan and it executes fine.
| sp_configure 'allow update', 0 |
2 comments:
Ran into this problem for the first time today and your post helped me fix it. Thank you.
http://sequelserver.blogspot.ca/2013/04/sql-server-2008-maintenance-plan-error.html
Post a Comment