Thursday, December 29, 2011

Manipulating database mail profile

here are samples I created for removing and creating database mail profile and account


if exists(select 1 from msdb.dbo.sysmail_principalprofile pp join msdb.dbo.sysmail_profile p on pp.profile_id=p.profile_id where p.name=N'SQLNotification-NDS')
begin
EXEC msdb.dbo.sysmail_delete_principalprofile_sp @principal_name = N'guest' , @profile_name = N'SQLNotification-NDS'
end
if exists(select 1 from msdb.dbo.sysmail_account where name=N'SQLMailACCT')
begin
EXEC msdb.dbo.sysmail_delete_account_sp @account_name =N'SQLMailACCT'
end
if exists(select 1 from msdb.dbo.sysmail_profile where name=N'SQLNotification-NDS')
begin
EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = N'SQLNotification-NDS'
end
go
EXEC msdb.dbo.sysmail_add_account_sp @account_name = N'SQLMailACCT',
@email_address = N'doNotReply@rocketGaming.com' ,
@display_name = N'doNotReply@rocketGaming.com' ,
@description = N'account used for sending out email from SQLServer' ,
@mailserver_name = N'10.0.0.111' , @mailserver_type = N'SMTP' , @port = 25
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name=N'SQLNotification-NDS', @description=N'DB mail profile'
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name=N'SQLNotification-NDS', @account_name=N'SQLMailACCT', @sequence_number=1
EXEC msdb.dbo.sysmail_add_principalprofile_sp @principal_name=N'guest', @profile_name=N'SQLNotification-NDS', @is_default=0
GO

No comments: