How Can I Tell If Ad-Hoc Updates to SQL Server System Catalogs Are Required?

Feature Only in Enterprise Edition   This feature is supported only in Visual C++ Enterprise Edition. For more information, see Visual C++ Editions.

Certain stored procedures that ship with Microsoft SQL Server directly modify the system catalogs. By default, SQL Server does not allow such ad-hoc updates to system catalogs. If you try to edit and save one of these stored procedures in a database that does not allow ad-hoc updates, the following may occur:

Note   Do not refresh from the database at this point.

To fix the problem so that you can edit the stored procedure and alter the system catalogue, your System Administrator must reconfigure the system catalogs. You can contact your database administrator and request that the database be reconfigured to allow ad-hoc updates of the system catalogs. The database administrator can change the default setting by running the following script:

sp_configure 'allow updates',1
go
reconfigure with override

or by running the following ad-hoc statements:

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

These statements permit ad-hoc modifications to stored procedures that alter system catalogs.

For more information, see “Error 259” in Chapter 25 “Handling Error Messages” of Part 8 “Troubleshooting” in Administrator’s Companion in the SQL Server online documentation.