Run a Stored Procedure when SQL Server starts

Although it is not a very good idea, you can set up a stored procedure to be started when SQL Server starts. Follow these steps:

Step 1: When you are creating the Stored Procedure, make sure the database context is set to Master database

USE master
GO
CREATE PROCEDURE dbo.usp_SomeProcForStart
AS
-- Stored Procedure Definition
GO

Note that your stored procedure cannot have any parameters and the owner is ‘dbo’

Step 2: The next step is to use the sp_procoption to set the stored procedure dbo.usp_SomeProcForStart to execute when SQL Server service starts:

EXEC sp_procoption 'usp_SomeProcForStart', 'startup', 'true'

To turn it off so that this stored procedure ‘does not’ execute when SQL Server service starts, use this command

EXEC sp_procoption 'usp_SomeProcForStart', 'startup', 'false'

Note: You may also want to take a look at sp_configure system stored proc in case your database is not configure for automatic execution of stored procedures.

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: