Results 1 to 12 of 12

Thread: Time driven Sp

  1. #1
    Join Date
    Apr 2007
    Posts
    63

    Unanswered: Time driven Sp

    How to execute a sp with respect to time ( For ex, sp should be executed at 12 AM everyday irrespective of system shut downs and log offs)

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Create a job
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2007
    Posts
    63

    Want more detail

    Could you pls explain in detail

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    http://www.databasedesign-resource.c...rver-jobs.html

    or look up jobs and SQL Agent in BoL.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2007
    Posts
    63

    Re: Time driven Sp

    Hi,

    Thank you v much for this really useful info. Jobs concept is new to me. Anyway I came to lknow abt this now.

    But at the last step, I want to trigger email to an email id. I could c that the corresponding option is disabed. When I create a new email operator, I coulndt enter other details and I can only enter the name of the email operator. How to connect and proceed further? pls do make me clear

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not sure- I don't use that feature. Sounds like you are using 2000 too - I am on 2005 I am afraid. Personally I log all this stuff to a table rather than use emails, especially for logging successful completion.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Look into making yourself a DTS package - these can fire off e-mails on success (you run SP's using the SQL EXEC command) - then schedule the DTS as a job.

    Or so I'm told

    EDIT: For clarification...
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2007
    Posts
    63

    Re:Time driven Sp

    Need some more info reg implementing DTS..

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Enterprise Manage >> Pick your server >> Data Transformation Services >> Local Packages >>RClick >> New Package...
    This should open the package designer.

    Create the connection to the database (windows auth or username/pw) by using the correct connection icon on the toolbar.

    When created, add a new SQL task (icon: yellow cylinder with red arrows)
    Stick your SQL statement in there....

    Add a send mail task (icon: letter)
    Do your stuff there.

    then add a workflow (file menu, workflow) and join the two.

    *shrugs* Any good to ya?
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by prem18
    Could you pls explain in detail
    Quote Originally Posted by prem18
    Need some more info reg implementing DTS..
    These are available in BoL - you would do well to read at least some basic information about new concepts and then ask any specific questions rather than requesting the full 101 each time.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You shouldn't need to jump out to a DTS package to send an e-mail on completion. Adding DTS to this mix needlessly complicates it.

    You need one of these options:
    A) Set up your SQL Server to send/recieve e-mails. I admit I am sketchy on the details of doing this, because is a network issue and not my area of expertise. Get your network admin to help you with it.

    B) Install xp_smtp_sendmail, a free utility available at http://sqldev.net/xp/xpsmtp.htm
    xp_smtp_sendmail is easy to install and easy to use. It enables you to send e-mail messages from sql code.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    B) Install xp_smtp_sendmail, a free utility available at http://sqldev.net/xp/xpsmtp.htm
    xp_smtp_sendmail is easy to install and easy to use. It enables you to send e-mail messages from sql code.
    Minor point but if you are using 2005 the inbuilt MS functionality is very, very easy to use. Needs enabling with the surface area configuration tool.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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