Friday, 8 June 2012


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.

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

Parameters details
Is the name of the procedure for which to set an option. Procedure is nvarchar(776), with no default.
Is the name of the option to set. The only value for option is startup.
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)

         INSERT INTO tbl_Server_Logon(Log_Dt)
         SELECT GETDATE() 


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.

WHERE NAME = 'scan for startup procs' 

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

No comments:

Post a Comment