Sometimes we need to send mails from inside a database, both through sql or thru .net codes. We can configure SQLserver to send mails by following these steps
-------CREATING A DEFAULT PROFILE--------------------------------------------
-----------------------------------------------------------------------------
--STEP 1: enable the Database Mail feature on the server
-----------------------------------------------------------------------------
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
-----------------------------------------------------------------------------
--STEP 2 create the account
-----------------------------------------------------------------------------
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MyMailAccount',
@description = 'Mail account for PMMA Database Mail',
@email_address = 'mayukh@maxx.com,
@display_name = 'PMMA DEV',
@username='mayukhd@maxx.com',
@password='abc123',
@mailserver_name = 'mail.maxx.com'
-----------------------------------------------------------------------------
--STEP 3 create a Database Mail profile
-----------------------------------------------------------------------------
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MyMailProfile',
@description = 'Profile used for database mail'
-----------------------------------------------------------------------------
--STEP 4 add the Database Mail account we created in step 2, to the Database Mail profile you created in step 3
-----------------------------------------------------------------------------
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MyMailProfile',
@account_name = 'MyMailAccount',
@sequence_number = 1
-----------------------------------------------------------------------------
--STEP 5 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 = 'MyMailProfile',
@principal_name = 'public',
@is_default = 1 ;
-----------------------------------------------------------------------------
--Test
-----------------------------------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail @recipients='mayukh@maxxzone.com',
@subject = 'My Mail Test',
@body = 'Test Body',
@body_format = 'HTML' ;
---------------------------------------------------------------------------
Following is the code to send databse mails through C# :
string SprocSendEmail = "msdb.dbo.sp_send_dbmail";
string msgSubject = "Message Subject";
string msgBodyFormat = "HTML";
//// *******Setup the Static Data[Message Body]*********************************
StringBuilder msgBody = new StringBuilder();
msgBody.Append("Start of Message Body <hr >");
msgBody.Append("Any other body text ");
msgBody.Append(" <hr >");
////*******Call the sp_send_dbmail SPROC*********************************
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = SprocSendEmail;
SqlParameter paramRecipients = new SqlParameter();
paramRecipients.ParameterName = "@recipients";
paramRecipients.SqlDbType = SqlDbType.VarChar;
paramRecipients.Direction = ParameterDirection.Input;
SqlParameter paramBody = new SqlParameter();
paramBody.ParameterName = "@body";
paramBody.SqlDbType = SqlDbType.VarChar;
paramBody.Direction = ParameterDirection.Input;
SqlParameter paramSubject = new SqlParameter();
paramSubject.ParameterName = "@subject";
paramSubject.SqlDbType = SqlDbType.VarChar;
paramSubject.Direction = ParameterDirection.Input;
SqlParameter paramBodyFormat = new SqlParameter();
paramBodyFormat.ParameterName = "@body_format";
paramBodyFormat.SqlDbType = SqlDbType.VarChar;
paramBodyFormat.Direction = ParameterDirection.Input;
sqlCommand.Parameters.Add(paramRecipients); //// parameter 0
sqlCommand.Parameters.Add(paramBody); //// parameter 1
sqlCommand.Parameters.Add(paramSubject); //// parameter 2
sqlCommand.Parameters.Add(paramBodyFormat); //// parameter 3
paramRecipients.Value = systemParametersCollection["mailingAddress"].ToString();
paramBody.Value = msgBody.ToString();
paramSubject.Value = msgSubject;
paramBodyFormat.Value = msgBodyFormat;
sqlCommand.ExecuteNonQuery();
Some additional web resources
How to configure SQL 2005 Database Mail
sp_send_dbmail (Transact-SQL)