Post reply

Stored proc executes on start up

  • June 10, 2008 at 1:26 am

    #186660

    Hi there,

    I just have a question about something on stored proc. In SQL 2000 when you open a stored proc via enterprise manager there is a checkbox at the bottom of the window that says 'Execute whenever SQL Server starts'.

    Does SQL 2005 have something like this too?

  • June 10, 2008 at 2:25 am

    #826485

    Yes you can make it possible by using sp_configure options. You need to enable scan for startup procedure option. Refer this links for more info,

    http://weblogs.sqlteam.com/mladenp/archive/2007/08/14/60280.aspx

    http://technet.microsoft.com/en-us/library/ms191129.aspx

    [font="Verdana"]- Deepak[/font]

  • June 10, 2008 at 2:40 am

    #826486

    Thanx Deepak

  • June 11, 2008 at 2:43 pm

    #827529

    I had this setup for a few procs in SQL 2000 and just moved these over to 2005 when upgrading, but the procs where not firing on start up, I did some digging and to me it looks like SQL 2005 is more finicky about the exact syntax layout.

    In 2000 I was able to run

    exec sp_procoption

    'YorProc',

    'startup',

    'on'

    and it worked, but when I ran this on 2005 it did not complain when run but it did not start up proc at start up, but when I ran

    exec sp_procoption

    @ProcName = 'YorProc',

    @OptionName = 'startup',

    @OptionValue = 'on'

    All was fine.

    Andrew

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply