How to configure Mail with MSSQL
- See Database Mail for how to enable Database Mail.
- After enabling Database Mail, you will need to create and configure the accounts. It's done in 3 steps:
- Create a profile with sysmail_add_profile_sp
- Create an email account with sysmail_add_account_sp
- Associate the email account to the profile with sysmail_add_profileaccount_sp
Applies to:
- Microsoft SQL Server 2005
Creating SQL email accounts
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Main',
@description = 'Main profile';
go
EXEC msdb.dbo.sysmail_add_account_sp
@account_name = 'System',
@email_address = 'system@smtp.system.com',
@display_name = 'sql',
@mailserver_name = 'smtp.system.com';
go
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Main',
@account_name = 'System',
@sequence_number = 1;
go
Command(s) completed successfully
- Create the profile
- Create the email account, with the email address on line 8 and the actual mail server on line 10. The email account must be enable on the actual mail server.
- Associate the the profile with the account number. The sequence number on line 16 determines the order in which accounts are used in the profile.
- Note that there is only 1 line replied by the mail server, in spite of the 3 commands executed.
Deleting SQL email accounts
- Similar commands are used, just replace the word add with delete:
- sysmail_delete_profileaccount_sp
- sysmail_delete_profile_sp
- sysmail_delete_account_sp
EXEC msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = 'Main',
@account_name = 'System';
go
EXEC msdb.dbo.sysmail_delete_profile_sp
@profile_name = 'Main';
go
EXEC msdb.dbo.sysmail_delete_account_sp
@account_name = 'System';
go
Command(s) completed successfully
- Notice that the order has changed, you first need to delete the profile account association, then you can delete the account and/or the profile.
- Note that there is only 1 line replied by the mail server, in spite of the 3 commands executed.
