For the sql server mail the Xp_smtp_sendmail has been used. So first that dll should be downloaded.
To setup the xp_smtp_sendmail:
1) For SQL Server 2000, download XPSMTP80.ZIP and unzip the files
2) Copy xpsmtpXX.dll into the SQL Server BINN directory.For SQL Server 2000 copy XPSMTP80.DLL.
For SQL Server 2000 the default location is "C:\Program Files\Microsoft
SQL Server\MSSQL\Binn"
3) Register the extended stored procedure using OSQL or SQL Query
Analyzer by executing:
exec sp_addextendedproc 'xp_smtp_sendmail',
'xpsmtp80.dll'
4) Grant rights to the correct set of users using OSQL or SQL Query
Analyzer by executing:
grant execute on xp_smtp_sendmail to public
By default only the member of the sysadmin role have execution rights
on the XP after it is being registered
A test case of how you can use this :
Script to Check the Offline Databases
1) This Script is to check the status of the Databases on the servers and send a Mail to the DBADMIN when any of the Database goes OFFLINE.
2) The script can be run as a Sql Server Agent Job on the Server and can be scheduled according to the requirements to run every one hour or every minute.
The Script is :
SET NOCOUNT ON
DECLARE @Msg VARCHAR(8000)
SELECT @Msg = ISNULL(@Msg + CHAR(13), '') + 'Database ' + z.Name + ' on machine ' + z.ServerName + ' is ' + z.Status + ' at ' + z.Now + '.'
FROM (
SELECT TOP 100 PERCENT @@SERVERNAME ServerName,
Name,
CONVERT(VARCHAR, DATABASEPROPERTYEX(Name, 'Status')) Status,
CONVERT(VARCHAR, GETDATE(), 109) Now
FROM master..sysdatabases
WHERE status & 512 = 512
ORDER BY Name
) z
PRINT @Msg
IF @Msg IS NOT NULL
EXEC master.dbo.xp_smtp_sendmail
@FROM = N'address',
@TO = N ' address',
@server = N'smtp.depaul.edu',
@subject = N'Status of the Database on Testsqlserver!',
@type = N'text/html',
@message = @Msg