In SQLSever 2005 msdb.dbo.sp_send_dbmail is the new way of sending email and it replaces xp_sendmail. Below are the steps how to setup Database Mail to use msdb.dbo.sp_send_dbmail.

— To check if “Database Mail XPs” option is turned ON
use master
go
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'Database Mail XPs'
go
sp_configure 'show advanced options',0
go
reconfigure
go

— If you need to enable “Database Mail XPs”
use master
go
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go
sp_configure 'show advanced options', 0
go
reconfigure
go

— Create new database mail account to hold SMTP information
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBMailAccount',
@description = 'Mail account for Database Mail',
@email_address = 'reply@oraclespin.com',
@replyto_address = 'reply@oraclespin.com',
@display_name = 'Database Administrator',
@mailserver_name = 'mailserver.com', — Mail server
@use_default_credentials = 1 — When this parameter is 1, Database Mail uses the credentials of the Database Engine

— Create new Database Mail profile
exec msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBMailProfile',
@description = 'Profile used for database mail'

— Adds the account to the profile, the sequence number determines the order
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBMailProfile',
@account_name = 'DBMailAccount',
@sequence_number = 1

— Grants permission for a database user or role to use a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DBMailProfile',
@principal_name = 'public', — if public profile it allows all principals in the database access to the profile
@is_default = 0 — Specifies whether this profile is the default profile

— Test email
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMailProfile',
@recipients='ajaffer@oraclespin.com',
@subject = 'My Mail Test',
@body = 'Test'

• SQL Server