Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2015
    Posts
    1

    Unanswered: scheduling a stored procedure without sql agent

    Hi, I'm developing a database for a newly form company and I'm still a student. I have a stored procedure I want to be executed on a daily basis, I understand that I need to do scheduling but I'm unclear on how to deal with it. Can anyone give me pointers or show me how to do this?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The "without SQL Agent" is a much harder nut to crack. Using SQL Agent, this takes a minute or two. Doing without SQL Agent is possible, and for an experienced DBA it could be done in a day but for a new DBA it will take more than a week to create the infrastructure that you need which is provided as part of SQL Agent.

    Using the SQL Agent:
    1. Use SSMS (SQL Server Management Studio)
    2. Connect to the SQL Instance that has the stored procedure that you want to schedule
    3. Click on the SQL Agent
    4. Right Click on the Jobs container, then pick New Job
    5. Provide a name for the new job
    6. Change the owner of the job to sa
    7. Click on the Steps container
    8. Click New Step
    9. Enter the name of the new step
    10. Change the database from master to the one where the stored procedure ought to run.
    11. Enter the Transact-SQL needed to run your procedure
    12. Click Ok
    13. Click on the Schedules container
    14. Click the New button
    15. Name your schedule
    16. Fill out the remainder of the dialog based on when/how you want your procedure to run
    17. Click the Ok button to save the new Schedule
    18. Click the Ok button to save the new Job
    19. Test, then Celebrate!

    I've been tempted to write a small book or a very large whitepaper for those who can't use SQL Agent, such as when you are using SQL Express. My problems are finding spare time for this project, and figuring out how to document the process so that readers at least realize what parts are dangerous. Different versions of SQL Server, different versions of Windows, and different environmental configuration issues make it really, REALLY hard to make this process safe and repeatable for new users.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2011
    Posts
    3
    you can create a batch file and schedule with task scheduler on server

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by sandeepmittal11 View Post
    you can create a batch file and schedule with task scheduler on server
    If you assume that nothing will ever go wrong, simply scheduling a task will work.

    If you need history, you can add some code to make event log entries.

    If you are going to that much trouble, then you should probably add some logging. By this time, you need to consider either PowerShell or .NET.

    Once you get that working, you're good... Until you apply a service patch that breaks something...

    Then you definitely need to use either .NET or PowerShell.

    At that point, you probably need to consider adding a way to notify you when the job fails. This implies adding an SMTP or another kind of messaging.

    Using SQL Agent looks a lot cheaper to me in the long run! ;-)

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I totally agree with Pat.
    Don't re-invent the wheel.

    The only other option is to use a 3rd party tool such as VisualCron, but that's not free either
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2016
    Posts
    24
    Provided Answers: 1

    use sqlcmd

    use sqlcmd to execute stored procedures

    SQLCMD - serverName - connection - command file yourfolder

Posting Permissions

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