sp_procoption procedure set or clears a stored procedure for automatic execution that runs every time an instance of SQL Server is started.
Syntax:
exec sp_procoption @ProcName = ['stored procedure name'],
@OptionName = 'STARTUP',
@OptionValue = [on|off]
– is the procedure name
OptionName – is the option name ( Currently, this does not really make sense as there is only one value permitted, which is ‘STARTUP’
OptionValue – is the value whether it is (ON or OFF)/(1 or 0)/(TRUE or FALSE)/(YES or NO)
Remarks:
1. Startup procedure must be in the master database and owned by DBO
2. It allows either SQL Procedures / CLR Procedures / Extended Procedures
3. The procedure cannot contain INPUT or OUTPUT parameters
4. It changes server configuration – “scan for startup procs”
Once the server is rebooted, then the “run_value” also gets changed to 1.
5. Execution of the stored procedures starts when all databases are recovered and the “Recovery is completed” message is logged at startup
6. Requires sysadmin permission
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
Like this:
Like Loading...
Related
2. It allows either SQL Procedures / CLR Procedures / Extended Procedures
3. The procedure cannot contain INPUT or OUTPUT parameters
4. It changes server configuration – “scan for startup procs”
Once the server is rebooted, then the “run_value” also gets changed to 1.
5. Execution of the stored procedures starts when all databases are recovered and the “Recovery is completed” message is logged at startup
6. Requires sysadmin permission
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
SQLZealots 
One thought on “sp_procoption in SQL Server”