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 named sp_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.

Posted by: MR. JOYDEEP DAS