SQL 101 : Email

SQL 2005

SQL Server 2005 replaces SQL Mail with Database Mail which allows you to send to a SMTP Server directly.

Enabling Database Mail on a server (via SAC | via TSQL)

Configuring Database Mail via TSQL


SQL 2000

SQL Server 2000 features two ways to access email functionality, 'SQL Mail' and 'SQLAgentMail'.

Both components are MAPI applications, hence MAPI must be present on the server e.g. through Microsoft Outlook.
Through MAPI, SQL 2000 can talk to Exchange or POP3 mail servers.
Setting this up requires Outlook to be installed on the SQL Server, configured with a mailbox and profile name (log in as the account sql runs under to do so).

How to Configure SQL Mail
http://support.microsoft.com/kb/263556

In practice, MAPI can be a real pain both in terms of setup and reliability (a must if using for system notifications).
Therefore multiple solutions exist on the web to send email via other delivery mechanisms from SQL Stored procedures.

CDONTS (think Windows NT) requires a local 'virtual' SMTP server to be installed which is configured to forward emails to a physical one.

CDOSYS (Windows 2000 onwards) can send to a local or remote SMTP server.
Both methods require elavated privileges or the sysadmin role.

CDOSYS example

How to send e-mail without using SQL Mail in SQL Server
http://support.microsoft.com/kb/312839