Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Talking Unanswered: server scheduled events?!

    I am not sure if I have posted something about this, but I can't believe there is not a single person out there that is using a procedure written in SQL in order to schedule sending of an email, if data hasn't been submitted.
    I have had some hits and some help from some people but I am pretty much still stuck.

    How is everybody else doing these sorts of thing,scheduling an email to be sent to a user if he/she hasn't submitted data,somebody must be doing it?
    A sample, help ,anything???

    Regards

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I'd do it as a dts package and then schedule that....

  3. #3
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    What do you mean by dts package,the thing is I was advised to do it this way because according to my colleage nothing else is as realible,and I am sort of being trained by him so I can't take a different approach if you know what I mean.

    Regards

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    What do you want to acheive, sending email using a SP or scheduling a job and send results via email?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Well in a way that is still for me to decide once I fully understand how that works.
    I was hoping to write the SP doing what it should do (basically check the database to see if data has been submitted and if it hasn't by the 5th of every month to send them a reminder on one side, but on the other side to collate all the data submitted and send it to the person that will analyse it), so yes, I would like to get procedure to check that and send results via emails.

    Thanks

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Defintely sounds like a prime target for a DTS package.

    A DTS pacakge is a Data Transformation Package. You don't have to transform data with it though, you can do a pile of things... such as run a query, check the results, send an email, run another query, send the results as an attachment.... which is basically everything you want to do.

    Then you schedule that package as a job and get it to run on the 5th of every month.

  7. #7
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    I do this right now, but through DTS as previously suggested. Instead of my application sending out Emails, I have a MsgQueue table that stores all the values of the email I want to send. Then every 30 minutes, I have a DTS package run and Email all the unsent messages from the table. This way I have a record of all emails sent, and can resend emails if necessary.
    That which does not kill me postpones the inevitable.

  8. #8
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    I've done things like this before. I think there's a lot of people who've done this with procedures. Why would you think not?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  9. #9
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Ok, could somebody tell me then where can I find a bit more about making/running a DTS package,anything remotely related to my example?

    Thanks

  10. #10
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Have you looked at www.sqldts.com?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  11. #11
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Ok,now that I have read something about DTS, could somebody run me through some important points when using DTS package in order to schedule emails, I hope I am not going on everybody nerves already.

    A short description of what you do, I have seen some examples but none on what I need to do.

    Regards and thanks for your help

  12. #12
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    1. When you are in the design mode of the DTS package, you need to select the email icon.
    2. This will open up the dialog for setting up an email. About the only thing you can do with this is type in a message, who you want to send it to, and attach a file. If you want to have a query attached, you need to have another piece of your DTS package do this. Let use know if you need help with this.
    3. Save the package.
    4. Schedule the package with SQLAgent. Here is an example string of how to schedule a DTS package.
    --Right-click on Jobs and select New Jobs.
    --Select an owner that created the DTS package. Needs to be an owner with permissions to xp_sendmail.
    --Go to Steps and create a new step. Select type of Operating System Command.
    --Paste in this as an example: DTSRun /S "(local)" /N "test" /E
    ---Test is the name of the DTS Package and will need to be replaced.
    ---Make sure you select the appropriate actions on the Advanced tab.
    --Go to the Schedules tab and schedule it.

    I'm pretty sure you're looking for something more specific than this. This will get you a DTS package that sends mail on a regular basis. If you need something more specific, let us know.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  13. #13
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    If you don't mind I would like to ask you more questions.

    1.where can i learn a bit more about these strings,what needs to be put in under "Command", as I have seen yours,and some more complex ones, and all I can see are numbers and letters,is there a way to learn that syntax?

    2.And I would like to know a bit more about "an attached query", do you mean creating a sql code in a procedure,or is there something else.

    3.Because I will need my scheduled job to check the database before emailing to users,how do I go about that?

    Thanks for helping me once again

Posting Permissions

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