Database Mail
Database Mail
Database Mail
Microsoft SQL Server 2005 includes a new and improved method for message delivery to and from the SQL Server. This feature, known as Database Mail, allows SQL to send and receive messages through SMTP delivery. Messages can be generated from within SQL and can include attachments from outside of the SQL environment. Database Mail uses the msdb database for storing configuration information about Database Mail
Cont
The Database Mail feature uses accounts to configure access to SMTP servers, and profiles to configure access to mail accounts. However, profiles and accounts can be mutually exclusive. You can create accounts without an association with a profile, and you can use the same account with multiple profiles, if necessary.
Cont
Database Mail is disabled by default. If this is the first time youve run this wizard, and have not manually enabled Database Mail, you will be prompted to enable it. Once youve enabled Database Mail, Once that information has been entered, you must configure at least one account that this profile will use. The ability to configure multiple accounts under a single profile helps guarantee the availability of the Database Mail feature to users who need to receive information, and path of delivery isnt relevant.
Cont..
To create a new account, click the Add button. In the New Database Mail Account screen,enter in an account name and description, and then information about the account, including the email address that the messages will originate from, the display name for that address, the reply-to address, and the name or IP address of the SMTP server. There is also a box where you can enter the port number used by the SMTP server. you know that your server uses a different port, you should use the standard SMTP port, 25. If your server uses Secure Sockets Layer (SSL) to protect the data in-transit, select the appropriate checkbox.
Cont..
The order in which the accounts are listed will determine the order of precedence when sending messages. Once youve entered in the information about the account, click OK to close the New Database Mail Account window. You can enter in more accounts to be used by the same profile, or you can continue on to the next screen by clicking Next.
Cont..
select the method of authentication the SMTP server requires. By default, Anonymous authentication is selected, but this is not the preferred method for most SMTP servers. If your SMTP server is Windows-based, you may be able to use Windows Authentication using Database Engine service credentials. Otherwise, you can use Basic authentication, providing a username and password manually.
Cont..
Next screen will ask you to provide information for a new Database Mail profile. Enter a name for the profile, and optionally a description to help identify the profile and how it will be used. On the Manage Profile Security screen, you can use the Public Profiles tab to elect to make the profile public.
sysmail_configure_sp
The sysmail_configure_sp stored procedure lives in the msdb database. EXECUTE msdb.dbo.sysmail_configure_sp MaxFileSize, 4194303, Max Size 4 MB
Cont
DECLARE @profileID INT ; EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = HumanResourcesMail, @description = Mail Profile for the Human Resources team., @profile_id = @profileID OUTPUT ; SELECT @profileID ;
Cont
The sysmail_help_profile_sp stored procedure will return information about the profiles created on the SQL Server. EXEC msdb.dbo.sysmail_help_profile_sp @profile_id=2 SELECT * FROM msdb.dbo.sysmail_profile
sysmail_add_account_sp
This stored procedure will create an account that is not associated with a profile. DECLARE @accountID INT; EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = Mail Sender, @description = Generic Account for sending mail, @email_address = mailsender@adventureworks.com, @display_name = SQL Database Mail Account, @mailserver_name = mail.adventureworks.com, @username = MailSender, @password = P@ssw0rd, @account_id = @accountID OUTPUT ; SELECT @accountID;.
Cont..
EXECUTE msdb.dbo.sysmail_help_account_sp EXECUTE msdb.dbo.sysmail_help_account_sp @account_id=2 SELECT *FROM msdb.dbo.sysmail_account
sysmail_add_profileaccount_sp
youve created a new profile and a new account. Now you can associate that account with that profile. Accounts can be associated with more than one profile, and each profile can be configured to use more that one account. To create the mapping, you can use the sysmail_add_profileaccount_sp stored procedure.
Cont..
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_id = 2, @account_name = MyAccount, @sequence_number = 1; EXECUTE msdb.dbo.sysmail_help_profileaccount_sp SELECT * FROM msdb.dbo.sysmail_profileaccount
sysmail_update_profile_sp
EXECUTE msdb.dbo.sysmail_update_profile_sp @profile_id = 2, @profile_name = HRMail, @description = Human Resources Mail Profile; EXECUTE msdb.dbo.sysmail_help_profile_sp;
sysmail_update_account_sp
EXECUTE msdb.dbo.sysmail_update_account_sp @account_id = 1, @account_name = SalesMail, @display_name = Microsoft SQL Server - Aughtfive, @replyto_address = administrator@adventureworks.com, @description = Sales Mail Account, @mailserver_name = aughtfive, @mailserver_type = SMTP, @port = 25, @username = NULL, @password = NULL, @use_default_credentials = 1, @enable_ssl = 0; EXECUTE msdb.dbo.sysmail_help_account_sp
sysmail_update_profileaccount_sp
If you want to change the sequence in which the accounts will be used within a profile, you can use the sysmail_update_profileaccount_sp stored procedure.
sysmail_add_principalprofile_sp
This stored procedure is used to control access to a mail profile. In order for the profile to be accessible, the profile will be made available to specific database principals within the msdb database. EXECUTE msdb.dbo.sysmail_help_principalprofile_sp @principal_name = public;
Stored Procedures
sysmail_update_principalprofile_sp sysmail_delete_principalprofile_sp sysmail_delete_profileaccount_sp sysmail_delete_account_sp sysmail_delete_profile_sp
Cont..
Deleting a profile removes a list of configured accounts, not the accounts themselves. If, however,your application is configured to use a mail profile youve recently deleted, once again,your SQL Server will be unable to send messages.