SQL Tips by Namwar Rizvi

August 16, 2007

Execute stored proecdure every time with the start of SQL Server

Remember good old days of DOS when you were able to execute any file by adding it autoexec.bat file? What if you need to perform some processing every time your SQL Server starts? The answer is sp_procoption By using this system stored procedure you can schedule a stored procedure to be executed every time your SQL Server get restarted.
Please keep in mind that the stored procedure:

  • must be in master database
  • must not have INPUT or OUTPUT parameters.

To use sp_procoption, you must be a member of sysadmin role.
Following is an example to schedule a simple stored procedure which increment a counter to give you an idea how many times your SQL Serever has been restarted.

Use master

–Create a test table in master database
Create table testTable (startupCount int)

–Create a stored procedure to schedule at startup
Create proc usp_IncrementStartupCount

If exists (Select top 1 * from testTable)
Update testTable Set startupCount=startupCount+1;
Insert into testTable values (1);


–Schedule the stored procedure for startup
exec sp_procoption ‘usp_IncrementStartupCount’,‘startup’,‘on’


–After restarting, execute the following query
Select * from testTable


1 Comment »

  1. it’s NOT wroKinG!!!!!!!!!!!?

    Comment by amarDeep — December 6, 2010 @ 9:51 AM | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: