Showing posts with label SQL Services. Show all posts
Showing posts with label SQL Services. Show all posts

March 7, 2010

SQL SERVER: Execute Stored Procedure when SQL SERVER is started

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)


September 15, 2009

SQL SERVER: Community Tech Days in our City at Ahmedabad on 3rd October 2009

Microsoft Community Tech Days are in 11 cities in INDIA with 19 insightful Technical Sessions.

These insightful Technical sessions are available in our city "Ahmedabad", Gujarat on 3rd October 2009.

So book your calendar for this day and be a part of this TechDays.

Limited seats are available , so please register yourself with this event:

Let me know if you have any problem in registrations.

July 15, 2009

SQL SERVER: Reset Setup Values, when SQL SERVER is started/restarted



We have a requirements to clear all setup values when SQL SERVER is started/restarted and we need to setup default values to setup table.

I found one Stored Procedure provided by MS SQL SERVER. Let me share it with all of you.

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 use this 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, lets take an example.


I have one Database called Test, I have created setup table:



CREATE TABLE SetupTable(
Seq INT IDENTITY,
Code VARCHAR(100)
)

Lets insert some default values to this table:



INSERT INTO SetupTable VALUES('A')
INSERT INTO SetupTable VALUES('B')
INSERT INTO SetupTable VALUES('C')
INSERT INTO SetupTable VALUES('D')

What I need to do is, I need to wipe out this values when SQL SERVER is started and fill it with the same default values, because these values might be updated by application.So, I created one stored procedure in master database, named,



CREATE PROC ClearAllData
AS

DELETE
FROM Test.dbo.SetupTable

INSERT INTO SetupTable VALUES('X')
INSERT INTO SetupTable VALUES('Y')
INSERT INTO SetupTable VALUES('Z')

and set up this stored procedure as auto executed every time when SQL SERVER is started as:



EXEC SP_PROCOPTION 
@ProcName = 'ClearAllData',
@OptionName = 'startup',
@OptionValue = 'true'



Now, restart SQL SERVICES, and you find that old values will be deleted and new values with ‘X’, ‘Y’, and ‘Z’ will be inserted automatically.


If now you want to stop it to execute automatically, we just need to execute this with “false” as:



EXEC SP_PROCOPTION 
@ProcName = 'ClearAllData',
@OptionName = 'startup',
@OptionValue = 'false'



I hope this is very clear to use this feature.


 


Reference : Tejas Shah (http://www.SQLYoga.com)