locked
How to start an sp_procoption "STARTUP" enabled Stored Procedure without restarting SQL Server? RRS feed

  • Question

  • Is there a way to "start" a stored procedure that has been configured for automatic startup via sp_procoption without having to restart SQL Server?

    Occasionally I'll need to make an adjustment and have to KILL the running session to do so, but I cannot seem to locate a way to turn it back on again without restarting SQL Server.

    I've tried the most obvious options, like calling the procedure from a query window, but that's not the correct way to go about this as it requires the query window to remain open.

    I've searched both here and on Google to no avail. I suspect I'm not asking the right question.

    Thanks in advance.


    Wednesday, July 30, 2014 10:16 PM

Answers

All replies

  • You can also set up the sp as a SQL Server Agent job and just start it programmatically:

    http://www.sqlusa.com/bestpractices2005/startjobfromstoredproc/

    msdb.dbo.sp_start_job does not require the query window to remain open. SQL Server Agent will keep it running.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012










    Wednesday, July 30, 2014 10:23 PM
  • So could you explain what you are doing with STARTUP option and what that has to do with a killing a session.

    May be what you need is a logon trigger ?

    Logon Triggers


    Satheesh
    My Blog | How to ask questions in technical forum



    Thursday, July 31, 2014 3:42 AM
  • Like you can "start" every other stored procedure, with EXECUTE (Transact-SQL)

    EXECUTE dbo.spYourAutoStartProcedure


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, July 31, 2014 6:34 AM
  • Thank you! This is the route I'm going to try. I'll reply back if I can confirm its success.
    Thursday, July 31, 2014 4:29 PM
  • I've considered this option, but I believe the SQL job might be the key here. Thank you!
    Thursday, July 31, 2014 4:30 PM
  • I've done this, but unfortunately this requires the query window to remain open which is not what I want.
    Thursday, July 31, 2014 4:31 PM
  • "msdb.dbo.sp_start_job does not require the query window to remain open. SQL Server Agent will keep it running."

    This seems to have done the trick, Kalman, thank you.

    Basically--in case anyone else stumbles across this same need--I created a job without a schedule that has a single step "EXEC master..MyProcedureName;"

    When I need to, I can start this task again by calling the job from a query window after which I can close Management Studio/Query Window.

    Calling the job from T-SQL:
    msdb.dbo.sp_startjob N'MyJobName';

    Friday, August 1, 2014 4:04 PM
  • I have the same problem. But with SQL Express I cannot you SQL Server Agent Jobs. Is there any other solution?
    Tuesday, March 3, 2020 10:14 AM