SQL Server 2005 Mail Configuration with Example.

use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure 
go
-------------------------
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'DatabaseBackupStatus',
    @description = 'To know about the database backup status.',
    @email_address = 'a.rameshk@yahoo.com',
    @display_name = 'From DatabaseBackupStatus',
 @username='sa',
 @password='sa',
    @mailserver_name = '192.168.41.11'
-----
EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = 'Profile for Database Backup',
       @description = 'Profile for Database Backup'
---------
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name =  'Profile for Database Backup',
    @account_name = 'DatabaseBackupStatus',
    @sequence_number = 1
------------------------
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name =  'Profile for Database Backup',
    @principal_name = 'public',
    @is_default = 1 ;
-------------------
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' Telling Database Status Successed'
EXEC msdb.dbo.sp_send_dbmail @recipients='a.rameshk@yahoo.com',
    @subject = 'Database Succeed',
    @body = @body1,
    @body_format = 'text' ;
And we can also find where databse mail has been installed or not by writing below query
SELECT * FROM sys.configurations 
In this value column is 0 then it is not installed or 1 Installed .Along with this query there is another query which tells Mail installed or not
sp_configure 'Database Mail XPs'
And if it gives config_value 1 Then it is intalled.Or not installed.
----To know about the status of all mails which are sent and failed we can write the below query
use msdb
GO
select * from sysmail_allitems
order by sent_date desc

--------To know about the status of failed mails which are sent and failed we can write the below query use msdb GO select top 20 * from sysmail_faileditems order by sent_date desc

--The below image will give us how to setup mail account and profile by using hotmail mail server.