Configure Database mail using T SQL script

-- using T-SQL

use msdb
declare @profile_id smallint
exec msdb.dbo.sp.sysmail_add_profile_sp
@profile_name=’DATABASEMAIL’,
@description=’DATABASEMAIL’,
@profile_id=@profile_id output;
select @profile_id
--–exec msdb.dbo.sysmail_help_profile_sp select * from msdb.dbo.sysmail_profile
declare @account_id int
execute msdb.dbo.sysmail_add_account_sp
@account_name=’samardeep’,
@email_address=’samardeep.skhera@diaspark.com’,
@display_name=’samardeep’,
@description=’Test Mail’,
@account_id=@account_id output;
select @account_id
/* to update
declare @account_id int
execute msdb.dbo.sysmail_update_account_sp
@account_id=1,
@account_name=’test’,
@email_address=’samae_99@gmail.com’,
@display_name=’ElixirCT’,
@description=’test mail’,
@replyto_address=’samae_99@gmail.com’,
@mailserver_name=’smtp.gmail.com’,
@mailserver_type=’SMTP’,
@port=25,
@username=samae_99@gmail.com,
@password=password,
@use_default_credentials=0,
@enable_ssl=0;
exec sysmail_help_account_sp
*/
–select * from sysmail_account exec sysmail_help_account_sp
–exec sysmail_help_profile_sp exec sysmail_help_account_sp
/*
exec msdb.dbo.sysmail_add_profileaccount_sp
@profile_id=1,
@account_name=’samardeep’,
@sequence_number=1
–or
@account_id=1,
@profile_name=’DATABASEMAIL’,
@sequence_number=2
*/
–exec sysmail_help_profileaccount_sp
–make the profile public
exec sysmail_add_principalprofile_sp
@profile_name=’DATABASEMAIL’,
@profile_id=1,
@is_default=0,
@principal_name=’public’
–exec sysmail_help_principalprofile_sp
exec msdb.dbo.sp_send_dbmail
@profile_name=’DATABASEMAIL’,
@recipients=’samae_99@yahoo.com’,
@subject=’hello…test mail’,
@body=’hi,
h r u’
SELECT * FROM sysmail_allitems


Enjoy

Difference beween SQLMail and Database mail

Its very small but very important to know difference between these two features of SQL Server. The difference between Database Mail and SQLMail is very confusing sometime. I will try to explain this concept below -

Database mail is newly introduced concept in SQL Server 2005 and it is replacement of SQLMail of SQL Server earlier version.

Database Mail has many improvements over SQLMail. Database Mail is based on SMTP (Simple Mail Transfer Protocol) and also very fast and reliable where as SQLMail is based on MAPI (Messaging Application Programming Interface).

Database mail depends on Service Broker so this service must be enabled for Database Mail.

Database Mail can be encrypted for additional security.

SQLMail is lesser secure as it can encrypt the message as well anybody can use SMTP to send email. Additionally, for MAPI to be enabled for SQLMail it will require Outlook to be installed.All this leads to potential security threat to database server.

So its good if you are using SQLMail, but this is right time to upgrade to Database Mail.

If you want to use the old SQLMail you can enable it with these commands.

EXEC sp_configure 'SQL Mail XPs', 1
GO
RECONFIGURE
GO

Hope this helps

Enjoy