go
reconfigure
go with overridesp_configure
go
reconfigure
Configuration Steps of SQL Server 2005 Database Mail
Right click on the Database Mail in order to display the context menu and select the Configure Database Mail menu item.
Database Mail Configuration Wizard runs and displays a welcome message.
Database Mail Configuration Wizard is used to enable Database Mail, manage security, and configure Database Mail system parameters
When you click Next
TITLE: Microsoft SQL Server Management Studio
------------------------------
The Database Mail feature is not available. Would you like to enable this feature?
You can enable the Database Mail feature by pressing the "Yes" button or by running the below sql sp_configure mail script
USE Master
GO
sp_configure 'show advanced options', 1
GO
reconfigure with override
GO
sp_configure 'Database Mail XPs', 1
GO
reconfigure
GO
sp_configure 'show advanced options', 0
GO
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Database Mail XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
If you run sp_configure command on the master database, you will see that the run_value for the "Database Mail XPs" is changed from 0 to 1 to enable this feature.
sp_configure
First step is creating a new profile. Enter a profile name and a description for the new profile. Then click the Add... button to complete the task.
Click Next button
Use this page to configure a public profile.
Profiles are either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (msdb) to send e-mail using that profile.
A profile may be a default profile. In this case, users or roles can send e-mail using the profile without explicitly specifying the profile. If the user or role sending the e-mail message has a default private profile, Database Mail uses that profile. If the user or role has no default private profile, sp_send_dbmail uses the default public profile for the msdb database. If there is no default private profile for the user or role and no default public profile for the database, sp_send_dbmail returns an error. Only one profile can be marked as the default profile
To send Database Mail users must also be a added to the DatabaseMailUsersRole database role in the msdb database using Management Studio or sp_addrolemember.
Use this page to configure a private profile.
Profiles are either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (msdb) to send e-mail using that profile.
A profile may be a default profile. In this case, users or roles can send e-mail using the profile without explicitly specifying the profile. If the user or role sending the e-mail message has a default private profile, Database Mail uses that profile. If the user or role has no default private profile, sp_send_dbmail uses the default public profile for the msdb database. If there is no default private profile for the user or role and no default public profile for the database, sp_send_dbmail returns an error.
Note:
To send Database Mail users must also be a added to the DatabaseMailUsersRole database role in the msdb database using Management Studio or sp_addrolemember.
Use this page to specify Database Mail system parameters. View the system parameters and the current value of each parameter. Select a parameter to view a short description in the information pane.
Options
Account Retry Attempts
The number of times that the external mail process attempts to send the e-mail message using each account in the specified profile.
Account Retry Delay (seconds)
The amount of time, in seconds, for the external mail process to wait after it tries to deliver a message using all accounts in the profile before it attempts all accounts again.
Maximum File Size (Bytes)
The maximum size of an attachment, in bytes.
Prohibited Attachment File Extensions
A comma-separated list of extensions which cannot be sent as an attachment to an e-mail message. Click the browse button (...) to add additional extensions.
Database Mail Executable Minimum Lifetime (seconds)
The minimum amount of time, in seconds, that the external mail process remains active. The process remains active as long as there are e-mails in the Database Mail queue. This parameter specifies the time the process remains active if there are no messages to process.
Logging level
Specify which messages are recorded in the Database Mail log. Possible values are:
Normal - logs only errors
Extended - logs errors, warnings, and informational messages
Verbose - logs errors, warnings, informational messages, success messages, and additional internal messages. Use verbose logging for troubleshooting.
Default value is Extended.
Reset All
Select this option to reset the values on the page to the default values.
Additional Information about the Retry Options
The sequence number in the Database Mail profile determines the order in which Database Mail uses accounts in the profile. This is configured by using the Move Up and Move Down buttons when configuring a profile in the Database Mail Configuration Wizard. For a new e-mail message, Database Mail starts with the account that has the lowest sequence number. Should that account fail, Database Mail uses the account with the next highest sequence number, and so on until either Database Mail sends the message successfully, or the account with the highest sequence number fails. If the account with the highest sequence number fails, the Database Mail pauses attempts to send the mail for the amount of time configured in the Account Retry Delay parameter of sysmail_configure_sp, then starts the process of attempting to send the mail again, starting with the lowest sequence number. Use the Account Retry Attempts parameter of sysmail_configure_sp, to configure the number of times that the external mail process attempts to send the e-mail message using each account in the specified profile.
If more than one account exists with the same sequence number, Database Mail will only use one of those accounts for a given e-mail message. In this case, Database Mail makes no guarantees as to which of the accounts is used for that sequence number or that the same account is used from message to message.
Run the below sql command from a query editor.
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMailProfile', @recipients = 'recipient@mail.com', @body = 'This is a sample email sent via Database Mail from a t-sql code script', @subject = 'Subject: Database Mail'
"Mail queued." response will be returned.
If you run the sp_send_dbmail stored procedure before enabling the Database Mail feature, you will get a response similar to below.
Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.
In such a case use sp_configure to set Database Mail XPs properties or enable the Database Mail feature using the Configure Database Mail Wizard.
If you enabled the database mail but getting the below error message, you might be using a wrong profile name in the sp_send_dbmail command.
Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid
In this case, run the Configure Database Mail Wizard. Then select "Manage Database Mail accounts and profiles" option in the Select Configuration
EXEC msdb.dbo.sp_send_dbmail @recipients='suruthikrishna@gmail.com',@body='yu', @subject ='Test Email from sandbox 94',@profile_name ='dbmail',@body_format = 'HTML' 'Database Mail XPs',1 'show advanced options',1
No comments:
Post a Comment