Just Learned - Execute Stored procedure at startup of SQL Instance

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

Execute Stored procedure at startup of SQL Instance

Jul 23 2012 12:00AM by Alpesh Patel

You can execute procedure at start up of SQL Server instance.

To do that follow steps below:

USE master;
GO
EXEC sp_configure 'show advanced option', '1';
GO
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'scan for startup procs', '1';

sp_procoption [ @ProcName = Procname] --'procedure' 
    , [ @OptionName = Startup] --'option' 
    , [ @OptionValue = On] --'value'

EXEC sp_configure 'show advanced option', '0';
GO
RECONFIGURE WITH OVERRIDE;

3 Comments

Any particular reason why you used "RECONFIGURE WITH OVERRIDE" instead of just "RECONFIGURE"?

The only time when I have used RECONFIGURE WITH OVERRIDE is when someone had switched on the "allow updates" switch (which is now marked as deprecated) - (http://beyondrelational.com/modules/2/blogs/77/posts/11282/ad-hoc-update-to-system-catalogs-is-not-supported-when-using-reconfigure.aspx).

commented on Jul 23 2012 2:02AM

Nakul Vachhrajani

4 · 33% · 10587

Interesting, I reviewed the BOL description of spprocoption:

Startup procedures must be in the master database and cannot contain INPUT or OUTPUT parameters. Execution of the stored procedures starts when all databases are recovered and the "Recovery is completed" message is logged at startup.

It also provides information on how to remove the SP from the startup, but what it doesn't tell you is how to get a list of SP's that have been marked for startup. Can anyone offer a query?

commented on Jul 23 2012 8:18AM

Lars007

1720 · 0% · 11

Lars007,

In SQL Server 2005 I was able to find the SPs which are marked as Start up like this:

SELECT 
    Routine_Schema, 
    Routine_Name, 
    Routine_Definition
FROM 
    MASTER.INFORMATION_SCHEMA.ROUTINES
WHERE 
    OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1

commented on Jul 23 2012 9:41PM

Vadivel

474 · 0% · 79

Your Comment

Sign Up or Login to post a comment.

"Execute Stored procedure at startup of SQL Instance" rated 5 out of 5 by 4 readers