Results 1 to 12 of 12

Thread: Startup Procs

  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: Startup Procs

    Does anyone know how to tell which procedures are called when SQL Server 2000 starts up if scan for startup procs is turned on?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I did not know this...

    Thanks

    Check out BOL: under:

    "scan for startup procs Option"

    And it should give you everything you need....

    Very cool...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Posts
    364
    Yeah, I checked that out and I'll I could find was how to set the autoproc flag on and off and how to change the order of execution. I was hoping there was a way to find all of the procs that have the autoproc setting turned on. Did I overlook something in BOL?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I can see the checkbox when I click on properties for a sproc...

    So that means there's got to be a way...

    It doesn't look like sp_procoptions does anything but sets the option...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    BOOYAH!

    Code:
    SELECT ROUTINE_NAME, OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') 
    FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_TYPE = 'PROCEDURE'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Slight modification:

    SELECT ROUTINE_NAME, OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStar tup') as execisstartup
    FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_TYPE = 'PROCEDURE' and
    OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStar tup') = 1

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not that it really matters because of so little data, but why the GROUP BY?

    Code:
      SELECT   ROUTINE_NAME
    	 , OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') AS execisstartup
        FROM   INFORMATION_SCHEMA.Routines 
       WHERE   ROUTINE_TYPE = 'PROCEDURE'
         AND   OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    The master database is loaded with sps so screening only the ones you want makes a difference.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by rnealejr
    The master database is loaded with sps so screening only the ones you want makes a difference.

    OK, you're confusing me now....

    Is that only for the current database?

    And yes, the original question was which ones are startup, so I agree with selection of only wherte = 1...but why the GROUP BY...

    Doesn't the WHERE do the same thing?


    Seems to.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    Only sps in master can be set for autoexecution.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Damn .....learned something new....again (a daily occurance)

    Thanks
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Sep 2003
    Posts
    364
    Thanks a bunch for the query. It sure would be nice to see the execution order too, but I can figure that out on my own if I have to.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •