Thursday, September 13, 2012

startup stored procedures in sql server

if you want to run some stored procedures during sql server startup you need to enable the following configuration option and then restart sql server instance.

sp_configure 'scan for startup procs',1
go
reconfigure

This is an advanced startup option so you need to first enable the 'advanced options' configuration parameter and then enable this.

once this is enabled, sql server scans for stored procedures if any to run whenever it is started.

To configure the stored procedure which you want to run it at startup, do this:
sp_procoption @procname,'startup', @optionvalue (on or off)

The SP should be in master database if you want to configure it for start up and it should not have any input/output parameters.