The sp_procoption system stored procedure is useful in setting the Stored Procedure for autoexecution – i.e it runs every time SQL Server service is started.
Here’s how to execute a Stored Procedure when SQL Server starts
EXEC sp_procoption @ProcName = 'usersp_CleanTables',
@OptionName = 'startup',
@OptionValue = 'true'
To disable the stored procedure again
EXEC sp_procoption @ProcName = 'usersp_CleanTables',
@OptionName = 'startup',
@OptionValue = 'false'
Note: For a Stored Procedure to be eligible to be executed when SQL Server starts, the stored procedure must be in the ‘master’ database and cannot contain INPUT or OUTPUT parameters.
comments
1 Response to "Execute Stored Procedure when SQL Server starts"Hi Thanks for sharing this. Similar to this, how I can execute a stored proc when SQL server shutdown.
Post a Comment