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,

Davethis would be far easier if you trashed the dts, built a stored procedure and then created a job to run the procedure.|||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|||Try running this sql through Query Analyzer:declare @.MyEmailString varchar(8000)

select @.MyEmailString = coalesce(@.MyEmailString + ',', '') + coalesce(email, '')
from employees
where role = 'MGR'

select @.MyEmailString|||Thanks Blindman.

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

Thanks again,

Dave|||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|||I wouldn't be surprised if this syntax didn't work in MySQL. Try posting your question in the MySQL forum.|||Lord, where's R937 when somebody mentions his favorite toy! You probably want the GROUP_CONCAT (http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html) 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