We have a requirements to execute Stored Procedure when SQL SERVER is started/restarted and we need to start some processes. I found that SQL SERVER provides a way to call Stored Procedure when SQL services are restarted.
SQL SERVER provides this SP: "sp_procoption", which is auto executed every time when SQL SERVER service has been started. I found this SP and it helps me to figure it out the solution for the request as following way.
Let me show you how to use it. Syntax to use SP:
EXEC SP_PROCOPTION
@ProcName = 'SPNAME',
@OptionName = 'startup',
@OptionValue = 'true/false OR on/off'
- @ProcName, should be Stored procedure name which should be executed when SQL SERVER is started. This stored procedure must be in "master" database.
- @OptionName, should be "startup" always.
- @OptionValue, this should be set up to execute this given sp or not. If it is "true/on", given sp will be execute every time when SQL SERVER is started. If it is "false/off", it will not.
That's it, I hope this is very clear to use this feature.
Reference : Tejas Shah (http://www.SQLYoga.com)
hello shah
ReplyDeletewhat is the main advantage of using this procedure in master database ?
let me explain the situation using this procedure?
regards
kalyan
Hi Kalyan,
ReplyDeleteThis procedure is needed in our production environment where we collect all sql trace into SQL tables. Whenever server is restarted, we need to manually run it and need to make sure that person who restarted services/server should start our performance test.
To make sure that person has started our performance test or not its not manageable and we cannot make sure about that.
Then I found this option which is very useful to us. By this option I will be free from worry that my trace will be started after services/server is restarted. Whenever services are started, it will also call my procedure and my trace will started.
There are several cases faced by some of developers. Please let us know by giving your Expereince/thoughts, so we all have better understanding.
Thanks,
Tejas