Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Community Wiki

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.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Create Stored Procedures That Run At SQL Server Startup

From Wiki

Jump to: navigation, search

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


  1. --Let's create our procedure
  2. USE master
  3. GO
  4. CREATE PROCEDURE prStartUp
  5. AS
  6. SELECT GETDATE()
  7. --You would do something real here
  8. --like deleting the data
  9. GO
  10.  
  11.  
  12. --Make the procedure execute when the server starts up
  13. sp_procoption prStartUp,startup,'on'
  14.  
  15. --This will return the proc name since we enabled the ExecIsStartup property
  16. SELECT name FROM sysobjects
  17. WHERE xtype = 'p'
  18. AND OBJECTPROPERTY(id, 'ExecIsStartup') = 1
  19.  
  20. --disable the execution of the proc on start up
  21. sp_procoption prStartUp,startup,'off'
  22.  
  23. --Let's check again, no rows should be returned now
  24. SELECT name FROM sysobjects
  25. WHERE xtype = 'p'
  26. AND OBJECTPROPERTY(id, 'ExecIsStartup') = 1


Contributed by: --SQLDenis 16:26, 9 June 2008 (GMT)


Part of SQL Server Admin Hacks

398 Rating: 2.2/5 (15 votes cast)