Results 1 to 10 of 10

Thread: Email

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Email

    Hi all me again I gotta question, the Sr executive asst has a number of reports that gets emailed to different department, only problem is those departments are on a totally different server. I am guessing that we need to start SQL AGENT Emailing MAPI to do this for us, of course I could be wrong. This is one of the stored procedures that creates one of the reports

    Code:
    CREATE PROCEDURE createSecurityListtobeEmailed
    AS
    SELECT SecurityListX."Last Name", SecurityListX."First Name", SecurityListX.MI, SecurityListX.Sex, SecurityListX.DOB, SecurityListX.IR#, SecurityListX."Issue Date", SecurityListX."Date served", SecurityListX.Duration, SecurityListX."Exp Date", SecurityListX."Reason for Exclusion", SecurityListX."Added Comment", SecurityListX.type INTO SecurityList
    FROM SecurityListX
    GROUP BY SecurityListX."Last Name", SecurityListX."First Name", SecurityListX.MI, SecurityListX.Sex, SecurityListX.DOB, SecurityListX.IR#, SecurityListX."Issue Date", SecurityListX."Date served", SecurityListX.Duration, SecurityListX."Exp Date", SecurityListX."Reason for Exclusion", SecurityListX."Added Comment", SecurityListX.type
    HAVING (((SecurityListX."Last Name") Is Not Null)) 
    GO

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It appears that this just does a simple aggregation from SecurityListX into SecurityList. I can't see that it has any return set, or that it emails anything anywhere.

    Are you sure that you aren't missing a few pieces to this puzzle ?

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    your correct

    yes pat there are a few pieces of the puzzle missing, this will create a list which will be made into a report that a webpage on the casinos server, that the security department can view. Now I guess I have inherited this db and I need to include in the stored procedures using Xp_sendmail but I am unsure how to do it, and what I am doing is returning nothing but errors. So if you please sir.....HELP?????

    Goodmorning Pat

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is actually way too complex for me to even hazard a guess about what you really need to do, based on what I know so far. A lot depends on your mail configuration, security requirements, etc., so there isn'ta "one size fits most" kind of answer.

    If all you want to do is run that statement, then send someone an email to tell them it ran (or failed), you can do that with just SQL Agent. If you want to extract the rows from the table and incorporate them into a mail message, then I'd suggest xp_sendmail.

    At the moment I'm hampered by my rather limited understanding of what you want!

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    At the moment I'm hampered by my rather limited understanding of what you want!
    And just when did that start to be a problem?

    Sorry...I have Notus Lotes...(piece of garbage)
    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 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    And just when did that start to be a problem?

    Sorry...I have Notus Lotes...(piece of garbage)
    I'm hampered by Bloats too. It does provide a great deal of casual entertainment as I'm trying to get some work done!

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Nevermind

    Nevermind Pat sorry to have bothered you

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Huh ?!?! Does that mean that you've solved the problem, or that you are going to look for another solution? Now you've given us enough to work on (via the snippets of VBA code) and you're going to take your ball and go home ?!?! Piddle!

    -PatP

  9. #9
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Oh sorry

    Misunderstood thought you were still unclear as was getting frustrated. I'm sure you have better things to do then to Mind read all day, since I couldnt get my point across. Had to retrieve more information was turning into a futile attempt...everyone was giving me bits and pieces.
    XP_sendmail is new to me not sure how to do it

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That is actually one of the biggest hurdles to taking responsibility for a new (to you) system. You have to gather all of the stray bits of knowledge tucked into nooks and crannies throughout the systems affected, and if you have any pity on the next poor soul to come along you'll document the living bejesus out of what confused you.

    If the next person has at least a few breadcrumbs to start from, they'll be a million miles further along than where you had to start from, and will hopefully utter prayers for you for the rest of their lives! Keep in mind that almost all systems are living things, they either grow or die, so the next person will have the chaos that you inherited plus everything that gets added during your tenure too. I've got a few systems that are exceptions that make great stories, but those are exceptions, not the rule by any means!

    -PatP

Posting Permissions

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