sending mail from Sql Server 2005

step1 Enable the Database Mail feature on Sql Server 2005 with the following Statements.

USE MASTER
GO
SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
GO
RECONFIGURE WITH OVERRIDE
GO
SP_CONFIGURE 'DATABASE MAIL XPS',1
GO
RECONFIGURE
GO


step 2. Using the sysmail_add_account procedure to Create Database account .

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Admin', -- Account Name on SMTP (User Defined)
@description = 'Mail Account for Database Mail',-- Account mail description. (User Defined)
@email_address = 'shivaraj.gk@gmail.com',-- Mail sender E-mail address (User Defined)
@display_name = ‘Admin', -- Mail Sender display name, which displays, when mail received by recipient (User Defined)
@username=’ ’, -- SMTP Sever User Name
@password='', -- SMTP Server Password
@mailserver_name = '190.160.110.50' -- SMTP Server Address.




Step3: Using the sysmail_add_profile procedure to create a Database Mail profile.
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'XXXXXXXX' –-Profile Name to add on SMTP Server(User Defined)
@description = 'Profile used for Database Mail'--Profile Description (User Defined)



Step4: Now execute the sysmail_add_profileaccount procedure, to add the Database Mail account, which created in step 2, to the Database Mail profile, which created in step 3.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'XXXXXXXX', –-Profile Name on SMTP Server(User Defined)
@account_name = 'Admin', Account Name on SMTP (User Defined)
@sequence_number = 1


Step5: Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile..

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'XXXXXXXX',
@principal_name = 'public',
@is_default = 1 ;


Step6: Now send a test email from SQL Server using sp_send_dbmail procedure..

DECLARE @Message AS VARCHAR(100)
SET @ Message ='Server :' + @@servername+ ' My First Database Email '
EXECUTE msdb.dbo.sp_send_dbmail
@recipients=' Shivaraj.gk@gmail.com', --Mail Recipient E-mail address (User Defined)
@subject = 'Hi, dear......Test Mail from Sql Server 2005',--Subject for the Mail
@body =@ Message, --Body of the Mail
@body_format = 'HTML'; --Body Format(HTML/Text)


Step7: You can check the configuration of the Database Mail profile and account using SQL Server Management Studio by right clicking Database Mail and clicking the Configuration..


Step8: The log related to Database Mail can be viewed by executing the statement below..


SELECT * FROM msdb.dbo.sysmail_event_log