In SQL Server, you can use the sysmail_delete_account_sp stored procedure to delete an existing Database Mail account.
You can delete an account by providing either its name or its ID (but you must provide either one or the other).
Continue readingIn SQL Server, you can use the sysmail_update_account_sp stored procedure in the msdb database to update your existing Database Mail accounts with T-SQL.
In SQL Server, you can use the sysmail_update_profile_sp stored procedure in the msdb database to update your existing Database Mail profiles with T-SQL.
In SQL Server, you can send emails using the sp_send_dbmail stored procedure in the msdb database.
One of the choices you have when executing this procedure is whether or not to include the results of a query.
Another choice you have is whether or not to send those results in an attachment.
Continue readingSQL Server provides us with the ability to send emails via its Database Mail solution. This includes a bunch of stored procedures that facilitate the configuration and sending of emails.
To send an email, use the sp_send_dbmail stored procedure. This procedure accepts a bunch of optional arguments, one of which allows you to send attachments.
Actually, there are two arguments that enable you to send attachments. The one you use will depend on the context.
They are:
@file_attachments – Allows you to specify a file to attach to the email.@attach_query_result_as_file – This is only applicable if you’re also using @query to email the results of a query.Examples below.
Continue readingWhen you use Database Mail to send emails with attachments, you’ll need to ensure that the attachment file size is within the allowable attachment file size.
If you need to send attachments that are larger than the attachment limit, you’ll need to increase that limit.
Fortunately, increasing the allowed attachment file size can be done with a single line of T-SQL code.
Continue readingSQL Server provides an easy way to email the results of a query to yourself (or to someone else).
To send email with T-SQL, you need to use the sp_send_dbmail stored procedure in the msdb database. This procedure accepts many arguments, one of which is the @query argument. That’s the argument that attaches the results of your query to the email.
To send email using Database Mail in SQL Server, a user must be a member of the DatabaseMailUserRole in the msdb database, and have access to at least one Database Mail profile.
To grant a user access to a Database Mail profile with T-SQL, use the sysmail_add_principalprofile_sp stored procedure in the msdb database.
In SQL Server, Database Mail uses profiles to send the email rather than accounts directly.
Therefore you must first create a profile, and create an account, then add the account to the profile. You also need to grant a user in the msdb database access to the profile.
To add a Database Mail account to a profile with T-SQL, use the sysmail_add_profileaccount_sp stored procedure in the msdb database.
If you’re setting up Database Mail to send emails in SQL Server, you will need to have at least one Database Mail account.
You can create Database Mail accounts with T-SQL by executing the sysmail_add_account_sp stored procedure in the msdb database.
The Database Mail account is the account that sends the email. For example, if you have an automated email that is sent whenever a SQL Server Agent job fails, this is the account that sends that email.
You can also specify things such as a separate “reply-to” email, a display name, etc.
You can have multiple accounts for various tasks if required. Simply execute sysmail_add_account_sp whenever you need to create a new account.