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

  • Article

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

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:

  • You edit a stored procedure such as sp_bindefault (that resides in the master database and performs ad-hoc updates to the system catalog). When you save the procedure, you receive the following error message: PROCEDURE: sp_bindefault 3700[Microsoft][ODBC SQL Server Driver][SQL Server] Ad-hoc updates to the system catalogs not enabled.

  • Visual C++ Enterprise Edition attempts to add the altered stored procedure you have edited. This operation fails, because the execution plan generated for this stored procedure will alter system catalogs, and by default, SQL Server prevents this.

    Because of the way Visual C++ updates the stored procedure, the database no longer contains this stored procedure; it has been deleted. You still have the text of the altered stored procedure, however, and can recover from this situation. You still have the local copy of the stored procedure, and after your system administrator alters the defaults on the database, you can save it.

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.