Storing sp_configure settings in a stored procedure

All SQL Server DBAS are familiar with sp_configure to change the instance global settings. However when you need to modify temporarily those settings from within your code, such as storing sp_configure settings in a stored procedure, the process can become awkward

You need to store the configuration setting you change but also the advanced configuration options if needed.

Fortunately, there are some little gems in Microsoft’s own code under the ‘Install’ folder of every instance. The instmsdb.sql is worth a read, as it:

  • gives you some insights on how msdb is created;
  • shows you that Microsoft sometimes uses good old methods like a ‘DEL’ command from the xp_cmdshell stored procedure to simply delete a file. (In SQL Server 2016 SP2, this is line 136).

This DEL statement is the source of our little useful piece of code, as Microsoft wants to put the xp_cmdshell enable status back to its previous state after issuing that DEL statement. Microsoft creates 2 temporary helpers as stored procedures called #sp_enable_component and #sp_restore_component_state

These are 2 good snippets which may be very handy.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.