sql server 2000 sending mails 1

[tempo1]

Hi everyone,
My table contains 2 columns:1. mame 2. email address.
Is there a way to write a script that will mail a message to the whole list without me sending the message to each mail address separately?
Thanks

 

[Glasgow]

This code will generate a semi-colon (usual separator for email clients) separated list of email addresses from a table that you could then pass as the email recipient field to whatever mechanism you are using to generate the email:

DECLARE @EmlLst AS VarChar(8000)
SELECT @EmlLst = COALESCE(@EmlLst + ';',';') + EmailAddress FROM MyTable
SELECT @EmlLst

If the list is likely to be more than 8000 characters long you may have to use an alternative approach - perhaps using text data type.

 

[tempo1]

Hi Glasgow
Sorry but i didnt understand...
The least i believe to have understood is :i gat a variable containning all the addresses.
Thanks anyway..

 

[ca8msm]

Have you tried running the example that Glasgow provided? It will make more sense to you if you run it and look at the results.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[chrissie1]

The problem with glasgow's aproach is spam-filters. For the rest it should work.

Christiaan Baes
Belgium

"My old site" - Me

 

[tempo1]

Hi,
Yes i ran it and recieved a variable that contains the mail addresses from my table.
I did more than that, i downloaded:

master.dbo.xp_smtp_sendmail

gave it my mail address as parameter and another mail address as another parameter, ran it (first 10 rows as a matter of fact) and recieved an error message saying:

Cannot load the DLL xpsmtp80.dll, or one of the DLLs it references. Reason: 126(error not found).

Thanks

 

[Glasgow]

Have you tried using xp_sendmail that ships with SQL2000?

The variable returned from my example could be passed to xp_sendmail as the first parameter - which is documented as expecting a semi-colon separated list.

 

[tempo1]

Hi Glasgow,
No i didnt try in the first place but having tried after reading your last notification i see "new horizons".
I'll have to spend some time learning that matter but at this moment i'm very gratefull to you.