Run a Stored Procedure when SQL Server starts


Steps to Run a Stored Procedure when SQL Server starts

As you all know, when SQL Server starts, it will first scan the registry to get the startup parameter values. By scaning registry it will find the master database files first and then make the master database online.

So if you want a procedure to execute when SQL Server starts automatically, you have to create that procedure in Master database only.

Step 1: Create a procedure in master database

Step 2: We have to use sp_procoption to set the stored procedure to execute when SQL Server service starts. Please see the example below.

EXEC sp_procoption 'procedure name', 'startup', 'true'

Step 3: Restart the SQL Service, to check the procedure output.

How turn off startup procedure?
You can turn-off the procedrue execution using below query.
EXEC sp_procoption 'procedure name', 'startup', 'false'

Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

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