Fairly often, we’re requested to extract some knowledge from a database and ship it by e mail. If the request repeats frequently, it’s onerous to carry out this boring similar process time and again. Because of this, you’ll need to ship emails with the predefined content material routinely.

In fact, emailing in SQL Server doesn’t have to solely be about exporting knowledge. You might merely need to preserve your self (or a colleague of yours) up to date when, for instance, a backup is completed or a programmed job fails miserably. With built-in functionalities, this may simply Send Email from MS SQL Server.

 

Enabling Database Mail in SQL Server Specific

We’ll use T-SQL to allow Database Mail. To start with, strive the next code:

If it really works, you’re good to go. Typically, you might hit an error informing you that ‘Database Mail XPs don’t exist. There’s a easy repair to that. Because it’s an advanced feature, we have to change the “present superior choices” from 0 to 1:

 

Configuring Database Mail

  1. Discover the ‘Administration’ folder within the SQL Server Administration console and right-click on ‘Configure Database Mail’.
  2. The Wizard will launch and welcome you with some particulars. Proceed to the subsequent web page by clicking on ‘Subsequent’.
  3. Now, it’s worthwhile to select what to do. Since we’re assuming Database Mail hasn’t been created earlier than, go along with the primary possibility.
  4. To arrange Database Mail, you’ll have to allow it first. If you happen to haven’t performed so earlier than, a popup will seem asking whether or not you need to allow Database Mail. Go for it.
  5. On the brand new display screen, you’ll see that no accounts are arrange. Go forward and add one by hitting the ‘Add’ button.
  6. A window will seem so you’ll be able to rapidly add the mandatory particulars of your mail account. If you’d like replies to be despatched to a unique e mail handle, put it underneath ‘Reply e-mail’. If you happen to go away it clean, responders will reply to the e-mail handle specified within the ‘E-mail handle’ discipline. ‘Description’ is elective.
  7. When you save the brand new profile, you’ll see it on the listing within the earlier window. You possibly can add extra accounts if you want and prioritize them. If the primary from the listing fails to send an email, for no matter motive, the subsequent account from the listing might be used.
  8. After clicking on “Subsequent”, you’ll see the listing of default settings. You possibly can modify them right here or preserve them intact. Every possibility is fairly nicely documented in Microsoft’s documentation.
  9. Lastly, you’ll see the abstract of every part you simply configured. If you happen to’re pleased with it, hit ‘End’ to wrap up the method. That’s all, Database Mail is configured! 

 

Sending emails with SQL

We use a extra standard methodology for sending emails with a system saved process referred to as sp_send_dbmail. This manner, you’ll be able to ship emails to specified recipients and embody them within the e mail question outcomes, attachments, or each. You’ll have to allow Database Mail first. Kindly comply with the instructions from the earlier paragraph.

Now we’ll have to arrange a Mail Account. On this instance, we used commonplace Gmail credentials however in fact, you’re free to make use of any account you’ve entry to. If you happen to need assistance deciding on which port it is best to use, take a look at our article on SMTP Ports.

If you happen to encountered any issues when executing an SQL process, first issues first, verify whether or not the SMTP server is ready up correctly. Confirm your credentials and take a look at sending a take a look at e mail with one other account.

Send Email from MS SQL Server

Leave a Comment