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
Go

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

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

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

End

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

———-STOP—————
—RESTART the SQL SERVER —
———-STOP—————

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

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: