sp_procoption
One of my friends asks me to run a stored procedure, when he starts the MS SQL server. His main problem is to run the stored procedure automatically when he starts the MS SQL Server.
This article is related to auto starts the stored procedure when the MS SQL Servers starts.
Microsoft provides a system stored procedure namedsp_procoption to solve this problem.
First we have to look the syntax of the system stored procedure and what type of parameters it takes.
Syntax
sp_procoption [ @ProcName = ] 'procedure'
, [ @OptionName = ] 'option'
, [ @OptionValue = ] 'value'
Parameters details
@ProcName
Is the name of the procedure for which to set an option. Procedure is nvarchar(776), with no default.
@OptionName
Is the name of the option to set. The only value for option is startup.
@OptionValue
Is whether to set the option on (true or on) or off (false or off). value is varchar(12), with no default.
Something we must have to remember before using the stored procedure
We must log in as sysadmin to use this system stored procedure. The procedure should be standard stored procedure with no INPUR or OUTPUT parameters. This stored procedure must be located in the master database.
Now I am trying to demonstrate it as an example.
In this example, we have a log table named "tbl_Server_Logon" which can take the MS SQL Server logon date and time.
USE master
CREATE TABLE tbl_Server_Logon
(id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Log_Dt DATETIME NOT NULL)
GO
CREATE PROCEDURE proc_LogOnDtls
AS
BEGIN
INSERT INTO tbl_Server_Logon(Log_Dt)
SELECT GETDATE()
END
GO
EXEC sp_procoption
@ProcName ='proc_LogOnDtls',
@OptionName ='STARTUP',
@OptionValue = 'ON'
-- To see the SQL Server Startup Date/Time
SELECT * FROM tbl_Server_Logon
You can modify the stored procedure as yourself to get the desired result. It is just a prototype for demonstrating the example.
Re-running our configuration check, we now see that the server is configured to check for startup procedures.
SELECT VALUE, VALUE_IN_USE, DESCRIPTION
FROM SYS.CONFIGURATIONS
WHERE NAME = 'scan for startup procs'
Output
VALUE VALUE_IN_USE DESCRIPTION
1 0 scan for startup stored procedures
Look at the VLUE_IN_USE = 0
When we restart the MS SQL Server and run the above SQL we get the output like that.
VALUE VALUE_IN_USE DESCRIPTION
1 1 scan for startup stored procedures
Now use this SQL to get the output
SELECT * FROM tbl_Server_Logon
id Log_Dt
1 2012-06-08 20:05:38.900
To remove the stored procedure as startup procedure
EXEC sp_procoption
@ProcName = 'proc_LogOnDtls',
@OptionName='STARTUP' ,
@OptionValue = 'off';
Hope you like it.
