miércoles, 24 de abril de 2013

sp_procoption : Automatic T-SQL Execution at Every Instance Startup

sp_procoption sets stored procedure for autoexecution. It runs stored procedures every time that SQL Server instance is started. (Applicable for SQL Server 2005 and above versions).

See the sintax:

sp_procoption [ @ProcName = ] 'procedure' 
    , [ @OptionName = ] 'option'
    , [ @OptionValue = ] 'value'

@ProcName = 'procedure'
Is the name of the procedure for which to set an option. procedure is nvarchar(776), with no default.
@OptionName = 'option'
Is the name of the option to set. The only value for option is startup.
@OptionValue = 'value'
Is whether to set the option on (true or on) or off (false or off). value is varchar(12), with no default.

Startup procedures must be in the master database and cannot contain INPUT or OUTPUT parameters.
Execution of the stored procedures starts when all databases are recovered and the "Recovery is completed" message is logged at startup.

Requires membership in the sysadmin fixed server role.

MSDN reference:

In example, we can register all times that server was restarted, see the script below:

Use master

CREATE TABLE dbo.ServerLog (StartupTime DATETIME) 

                INSERT dbo.ServerLog
                               SELECT GETDATE()

Mark the stored for autoexecution

@ProcName = 'TimeLog',
@OptionName = 'startup',
@OptionValue = 'true'

Disable autoexecution

EXEC sp_procoption
@ProcName = 'TimeLog',
@OptionName = 'startup',
@OptionValue = 'off'

The example inserts current date in table ServerLog every time server restarts.

Only objects in the master database owned by dbo can have the startup setting changed.