Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Create Stored Procedures That Run At SQL Server Startup
From Wiki
Let's say you have a table and you want to make sure that you clear it every time SQL Server is restarted What would be the easiest way to accomplish that? Well you can create a procedure and have it execute every time the SQL Server is restarted The procedure has to be created in the master database, after it is created you have to use sp_procoption to have the procedure execute when SQL Server starts up
- --Let's create our procedure
- USE master
- GO
- CREATE PROCEDURE prStartUp
- AS
- SELECT GETDATE()
- --You would do something real here
- --like deleting the data
- GO
- --Make the procedure execute when the server starts up
- sp_procoption prStartUp,startup,'on'
- --This will return the proc name since we enabled the ExecIsStartup property
- SELECT name FROM sysobjects
- WHERE xtype = 'p'
- AND OBJECTPROPERTY(id, 'ExecIsStartup') = 1
- --disable the execution of the proc on start up
- sp_procoption prStartUp,startup,'off'
- --Let's check again, no rows should be returned now
- SELECT name FROM sysobjects
- WHERE xtype = 'p'
- AND OBJECTPROPERTY(id, 'ExecIsStartup') = 1
Contributed by: --SQLDenis 16:26, 9 June 2008 (GMT)
Part of SQL Server Admin Hacks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.