Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Location
    Wexford, Ireland
    Posts
    13

    Question Unanswered: Building a mailing list

    Hi folks,

    I'm building a DTS package which needs to mail a list of users nightly

    The mailing list needs to be dynamic so I'm using the dynamic properties tab to populate the To, From etc. fields in an SMTP DTS task.

    I need to construct a script which will run within the DTS package and build a mailing list file from a table of users in the connected db. The Dynamic properties task will then pull from this data file when populating the 'To' field.

    The basic select statement is simple (e.g. SELECT email FROM employees WHERE role = 'mgr') however I need the output to be a single line of email addresses separated by commas (e.g. Email1,Email2,Email3....etc).

    I'm a bit unsure on how to go about doing / writing this.

    Can anyone help?

    Thanks,

    Dave

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    this would be far easier if you trashed the dts, built a stored procedure and then created a job to run the procedure.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2005
    Location
    Wexford, Ireland
    Posts
    13
    Thought about that but my Stored Proc skills are nothing to write home about. If you have anything I could tweak to suit my needs I'd be grateful.

    Thanks,

    Dave

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try running this sql through Query Analyzer:
    Code:
    declare	@MyEmailString varchar(8000)
    
    select	@MyEmailString = coalesce(@MyEmailString + ',', '') + coalesce(email, '')
    from	employees
    where	role = 'MGR'
    
    select	@MyEmailString
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Jan 2005
    Location
    Wexford, Ireland
    Posts
    13
    Thanks Blindman.

    Still trying to get my head around the syntax and use of Coalesce but it works.

    Thanks again,

    Dave

  6. #6
    Join Date
    Jan 2005
    Location
    Wexford, Ireland
    Posts
    13
    Hi Blindman,

    I need to run the code below against a MySql database.
    It works fine when I modify it for an MS SQL db however I'm getting syntax errors when running it against a MySQL (5.1.9) db.

    Any ideas?

    Thanks,

    Dave

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I wouldn't be surprised if this syntax didn't work in MySQL. Try posting your question in the MySQL forum.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Lord, where's R937 when somebody mentions his favorite toy! You probably want the GROUP_CONCAT function in MySQL.

    <Afterthought>
    After posting this, I realized that Rudy would probably pitch a fit, mostly because he enjoy's pitching fits. Note that I do NOT consider MySQL to be a toy, Rudy just likes to play with it because MySQL offers functions that violate the basic rules of relational algebra (such as the GROUP_CONCAT() function). This violation of hte rules doesn't make MySQL a bad product in any way, although I see it as removing MySQL from the category of Relational databases since it doesn't follow the basic rules required for a relational database.

    -PatP
    Last edited by Pat Phelan; 05-25-06 at 10:51.

Posting Permissions

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