Tuesday, March 6, 2012

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