sp_procoption in SQL Server

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]

ProcName – 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!

One thought on “sp_procoption in SQL Server”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s