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
