Sending external email from within a Scheduled Job

[teddysnips]

I'm not a newbie, but I've not been able to find out how to do this
particular task, and BOL isn't being too cooperative.

My application sits on a SQL Server backend (SQL Server 2000).

The application is a Document Distribution system. Some subscribers
have elected to be notified by e-mail that a new revision of a document
is available.

I want to run a daily job that would search for new revisions and email
any electronic subscribers. Pseudo code example:

DECLARE MailList CURSOR FOR

SELECT
EMailAddress
FROM
Customers
INNER JOIN DocumentDistribution ON
Customers.CustomerID = DocumentDistribution.CustomerID
WHERE
DocumentDistribution.DocumentID = @DocumentID AND
Customers.EMailEnabled = 1

OPEN MailList

FETCH NEXT FROM MailList
WHILE @@FETCH_STATUS = 0
BEGIN
/* Format and send the email to the customer here */
FETCH NEXT FROM MailList
END

CLOSE MailList
DEALLOCATE MailList

What do I need to do on the server to make this work?

Many thanks in advance

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

[Simon Hayes]

<te********@hotmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

I'm not a newbie, but I've not been able to find out how to do this
particular task, and BOL isn't being too cooperative.

My application sits on a SQL Server backend (SQL Server 2000).

The application is a Document Distribution system. Some subscribers
have elected to be notified by e-mail that a new revision of a document
is available.

I want to run a daily job that would search for new revisions and email
any electronic subscribers. Pseudo code example:

DECLARE MailList CURSOR FOR

SELECT
EMailAddress
FROM
Customers
INNER JOIN DocumentDistribution ON
Customers.CustomerID = DocumentDistribution.CustomerID
WHERE
DocumentDistribution.DocumentID = @DocumentID AND
Customers.EMailEnabled = 1

OPEN MailList

FETCH NEXT FROM MailList
WHILE @@FETCH_STATUS = 0
BEGIN
/* Format and send the email to the customer here */
FETCH NEXT FROM MailList
END

CLOSE MailList
DEALLOCATE MailList

What do I need to do on the server to make this work?

Many thanks in advance

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

There are several options, including SQL Mail, xp_smtp_sendmail, or an
external script. SQL Mail is the "standard" way to send email from MSSQL,
using the xp_sendmail procedure - see Books Online for more details, and
this article:

http://support.microsoft.com/kb/263556/EN-US/

However, SQL Mail has some issues - you need to install a MAPI client (eg
Outlook) on the server, which many DBAs are unwilling to do; it works best
with Exchange and can be awkward with SMTP; it doesn't support HTML mail
etc. As a result, xp_smtp_sendmail is a popular replacement, because it's a
single DLL and therefore less 'intrusive':

http://www.sqldev.net/xp/xpsmtp.htm

Or simply write your own mailing script in a language like C#, Perl etc.,
and launch it from a scheduled task in MSSQL. If you need to compose HTML or
do other text formatting, that may be a better option than doing everything
in SQL anyway.

Simon