Sending emails from SQL Server

Very often, we’re asked to extract some data from a database. If the request repeats regularly, it’s worth looking into automating it so you don’t have to perform this boring procedure over and over again. For this reason, you’ll probably want to send emails from SQL Server with the predefined content. And it’s not that hard to set up.

Of course, emailing in SQL Server doesn’t need to only be about exporting data. You may simply want to keep yourself (or a colleague of yours) updated when, for example, a backup is finished or a programmed job fails miserably. With built-in functionalities, this can easily be done too.

We cover several different approaches and let you choose which works best for your needs.

Sending emails with a built-in Wizard

The first approach doesn’t require any code as you’re guided through the entire setup by a built-in Wizard. 

The first thing you need to take care of is email infrastructure. For that, you’ll have to set up a Database Mail functionality in SQL Server. If you’re using SQL Server Express, you’ll need to enable Database Mail first as it’s disabled by default.

(Optional) Enabling Database Mail in SQL Server Express

Skip this part if you’re not on SQL Server Express but, for example, SQL Server 2017.

We’ll use T-SQL to enable Database Mail. First of all, try the following code:

sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

If it works, you’re good to go. Often, you may hit an error informing you that ‘Database Mail XPs don’t exist’. There’s a simple fix to that. Since it’s an advanced feature, we need to change the “show advanced options” from 0 to 1:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

Copy

If it’s executed properly, try the earlier code and proceed when both values are set to ‘1’.

Configuring Database Mail

Find the ‘Management’ folder in SQL server management console and right-click on ‘Configure Database Mail’.

 

The Wizard will launch and welcome you with some details. Proceed to the next page by clicking on ‘Next’.

 

On the following screen, you need to choose what to do. Since we are assuming Database Mail hasn’t been created before, go with the first option as in the screenshot below.

 

To set up Database Mail, you’ll need to enable it first. If you haven’t done so before, a popup will appear asking whether you want to enable Database Mail. Go for it.

On the new screen, you’ll see that no accounts are setup. Go ahead and add one by hitting the ‘Add’ button.

A window will appear so you can quickly add the necessary details of your mail account. If you want replies to be sent to a different email address, put it under ‘Reply e-mail’. If you leave it blank, responders will reply to the email address specified in the ‘E-mail address’ field. ‘Description’ is optional. 

 

Once you save the new profile, you’ll see it on the list in the previous window. You can add more accounts if you wish and prioritize them. If the first from the list fails to send an email, for whatever reason, the next account from the list will be used.

The following screen will bring you to the list of profiles, split into public and private ones.

  • Private Profiles are available to any user or role with access to the database.
  • Private Profiles can be restricted to specific users only.
 

After clicking on “Next”, you’ll see the list of default settings. You can modify them here or keep them intact. Each option is pretty well documented in Microsoft’s documentation.

 

Finally, you’ll see the summary of everything you just configured. If you’re happy with it, hit ‘Finish’ to wrap up the process.

 
 

That’s all, Database Mail is configured! 

You can test whether sending emails works straightaway. Right-click on Database Mail and select ‘Send Test E-mail’ from the list. 

 

Here, type in the email address you want to send the test to along with the desired content and send!

Inspect Your Emails

Sending emails with SQL Server Agent

The most straightforward way to send emails from SQL Server is by creating a new SQL Agent Job and setting SQL to send emails about the job’s status. For that, you can use the profile you just created.

To set up, you can use another simple Wizard. Right-click on SQL Server Agent and pick ‘Properties’ from the list.

 

Here, you’ll need to modify some settings. In the ‘Alert System’ tab nested under ‘Select a page’, do the following:

  • Tick ‘enable mail profile’.
  • In this section, choose ‘Database Mail’ from the dropdown list for ‘Mail system’ and pick the profile you just created from the ‘Mail profile’ dropdown.
  • Tick ‘Replace tokens for all job responses to alerts’.
 

Now, you need to set up an Operator that will be a recipient of your emails. Head back to the list under SQL Server Agent, right-click on ‘Operators’ and pick ‘New Operator’.

In the new menu, you simply need to insert the recipient’s email address, a. Adding a ‘Pager e-mail name’ is optional.

 

To apply the changes, you’ll need to restart the service of SQL Server Agent and then pick ‘Start’ to resume our project.

Once you’re back, it’s time to create a new SQL Job. Right-click on ‘Jobs’ and pick ‘New Job…” from the top of the list.

 

Here, tick the box to the left of ‘E-mail’ field. Choose your newly created profile from the dropdown list and then select the condition. In our example, we’ll want to send an email to ‘Operator’ when the job completes.

 

Save and move on to the last stage. You should have a profile set up along with an Operator and a Job. Now you only need to create a Maintenance Plan. To no surprise, you can do it with yet another Wizard.

Find the ‘Maintenance Plans’ menu and right-click on ‘New Maintenance Plan…”. The new screen gives you some ideas about the kind of things you’ll be able to set up.

 

You can name it right away and then move on.

 

In the next step, you can specify exactly what the task this time will be.

 

You’ll see your newly created task on the list. If you created some before, you’ll be able to prioritize them at this stage.

 

At the next stage, you can select which databases you want the task to be performed on. On the same screen, you can also choose where backups should be saved.

In the ‘Destination’ tab, you can choose exactly where on your drive backups should be saved. Use ‘Options’ tab to review the default options and adjust them to your liking.

 

On the following screen, you can choose to create a report to a text file (and pick a path where it should be saved). But what interests us the most is sending an e-mail report. Here, pick the profile you want to use for sending these reports.

At the last step, you’ll see all the settings you just set up. If everything checks out, click on ‘Finish’ and just wait for the first email to arrive in the specified inbox.

 

Sending emails with SQL

Another method for sending emails is with a system stored procedure called sp_send_dbmail. This way, you can send emails to specified recipients and include in the email query results, attachments or both. 

First things first, if you didn’t follow the first, wizard-based method we covered and you’re using SQL Server Express, you’ll need to enable Database Mail first. Kindly follow the instructions from the earlier paragraph.

Now we’ll need to set up a Mail Account. In this example, we used standard Gmail credentials but of course, you’re free to use any account you have access to. If you need help deciding on which port you should use, check out our article on SMTP Ports.

-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Gmail',
    @description = 'Mail account for sending outgoing emails.',
    @email_address = '<xyz@gmail.com>',
    @display_name = 'Piotr @ Mailtrap',
    @mailserver_name = 'smtp.gmail.com',
    @port = 465,
    @enable_ssl = 1,
    @username = '<gmail_username>',
    @password = '<gmail_pass>' ;
GO

Immediately after this is executed, you can view the new records in the sysmail_account and sysmail_server tables.

We now have an account. Now we need to set up a new profile in Database Mail and attach it to our new account.

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Emalis from SQL',
    @description = 'Profile for sending emails from SQL Server, with Gmail account.' ;
GO

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Emalis from SQL',
    @account_name = Gmail',
    @sequence_number =1 ;
GO

You’ll notice quickly that the sysmail_profile and sysmail_profileaccount tables have been updated.

Now that we have everything set up, we can proceed with a test email:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ''Emalis from SQL',
    @recipients = '<bill@mailtrap.io>',
    @copy_recipients = '<jane@mailtrap.io>',
    @blind_copy_recipients = '<frank@mailtrap.io>',
    @body = 'what’s up?!',
    @subject = 'Hey folks',
	@from_address = '<from email>',
	@reply_to = '<from email>'

Once you hit ‘ENTER’, the email will appear in the queue in the sysmail_mailitems tables and move to sysmail_log table shortly after regardless of the result. Of course, if you have set everything up properly, it should also land in the inbox of your recipients.

Customizing emails in SQL

OK, that was easy, but we came here to set up emails that are a bit more sophisticated. Let’s look at some of the available arguments we can use in such emails.

[ @file_attachments = ] 

You can add any attachments to your email by specifying their absolute paths here. Each file can be a maximum of 1MB in size.

[ @query = ]

This way you can execute any Transact-SQL query and attach it in the email body. Be aware that the query you specify here is executed in a separate session so you can’t use any local variables specified in the script on this occasion.

If you specify a query, you can also use an [ @execute_query_database = ] argument to specify a database on which the stored procedure will run the query

To specify if the results of a query should be sent as an attachment or be inserted into an email body, use an [ @attach_query_result_as_file = ] argument. The value of ‘0’ indicates that the results should go into the body – this is also what will happen if this argument is skipped. The value of ‘1’ indicates an attachment.

There are a number of further customizations you can perform on the query results to, for example, include or exclude headers or decide how to handle errors. Review them all in Microsoft’s Documentation.

Sending emails with a CLR Stored Procedure

The third approach to the problem is with CLR Stored Procedure. There can be a number of reasons why you would want to do this. For example, maybe you’re using SQL Server Express which doesn’t offer support for Transact-SQL and you’re not able to run sp_send_dbmail. Or maybe you need more control of the email sending process or lack sufficient rights on the instance level.

Whatever the reason, setting up a SQLCLR Stored Procedure for sending emails is fairly easy.

If you haven’t written any CLR procedures before, be sure to review Microsoft’s Documentation for tips on getting started and basic examples. 

Also, you need to enable CLR first to use the following procedures. CLR integration in SQL Server is off by default. Here’s how to enable it.

You’re free to use either VB.NET or C#.NET to write the procedure. We’ll stick with the first one here. 

Imports System.Net
Imports System.Net.Mail

Public Class StoredProcedure
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub spSendMail(ByVal recipients As String, ByVal subject As String, ByVal from As String, ByVal body As String)
Dim mySmtpClient As SmtpClient

Using myMessage As New MailMessage(from, to)

myMessage.Subject = subject
myMessage.Body = body

myMessage.IsBodyHtml = True

mySmtpClient = New SmtpClient("smtp.gmail.com")
mySmtpClient.Credentials = New NetworkCredential("my_email_address", "my_password")
mySmtpClient.Send(myMessage)

End Using
End Sub
End Class

What happened here:

  • We created a procedure that’s supposed to send an email with defined ‘Subject’ and ‘Body’, using ‘From’ and ‘To’ variables as a sender and a recipient, respectively
  • We also added our example credentials, including the SMTP port, login, and password. Of course, Gmail is just an example here.

Save the code to C:\SendEmail.vb.

Now, let’s compile it on the machine the code will run on. An example command link may look as follows if you used vbc.exe application:

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\vbc /target:library C:\SendEmail.vb

You’ll find the compiled file at C:\SendEmail.dll

In the following step, you’ll need to create the assembly and the stored procedure in SQL Server. 

USE msdb // change if you use a different database
GO

CREATE ASSEMBLY SendEmail FROM 'C:\SendEmail.dll'
WITH PERMISSION_SET = UNSAFE
GO

CREATE PROCEDURE [dbo].[spSendMail]
@recipients [nvarchar](4000),
@subject [nvarchar](4000),
@from [nvarchar](4000),
@body [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SendEmail].[StoredProcedure].[spSendMail]

If you’re seeing an error when trying to compile, try to alter the code with the following and try again:

ALTER DATABASE msdb SET trustworthy ON

Finally, test whether everything works as expected. Here’s an example code using the same parameters we indicated above:

EXEC spSendMail @recipients = 'kate@mailtrap.io, pete@mailtrap.io',
@subject = 'Hey, this works too!',
@from = 'piotr@mailtrap.io', @body = 'No one expected the SQL Revolution!'

Of course, you can use a lot more sophisticated arguments in your procedures, such as:

  • @fileAttachments – include attachments in your email, specifying their absolute paths. 1MB per file allowed.
  • @mailCC and @blindCopyRec – add emails in cc and/or bcc,
  • @replyAddress – accept responses at another address than specified in the ‘From’ field. One address allowed.
  • @bodyHtml – allow or disallow HTML. Allowed by default.

Here’s an example using some of these arguments:

EXEC spSendMail @recipients = 'kate@mailtrap.io',
@replyAddress = ‘claire@mailtrap.io’
@blindCopyRec = ‘pete@mailtrap.io’
@bodyHtml = ‘0’
@subject = 'Don’t tell Pete but',
@from = 'piotr@mailtrap.io', @body = 'No one expected the SQL Revolution!'

Debugging and testing SQL Server emails

If you encountered any problems when executing an SQL procedure, first things first, check whether the SMTP server is set up properly. Verify your credentials and try sending a test email with another account (preferably from another provider too). If everything’s fine on this end, launch Visual Studio and start debugging your code.

When using either of the first two methods, Database Mail could be the source of the problem. There are a lot of things that could have gone wrong. This guy has a compelling list of 21 troubleshooting steps you should consider.

Finally, if emails are sent but you’re not entirely sure whether they’re set up as expected, you probably want to route them to a dedicated environment for testing. One such example is Mailtrap, a pre-production email testing platform.

With Mailtrap, you can gather all your emails sent from SQL Server in a single inbox. Here, you can check them for possible HTML issues,  and validate the links or attachments. You can also bcc Mailtrap on all your outgoing emails. This way, you’ll get a copy of each email sent so you can quickly inspect these emails and fix them right away.


I hope our guide on what to do if sql server database mail not sending was useful for you! It was initially published in the Mailtrap blog by Piotr Malek.

What is GEEK

Buddha Community

Cayla  Erdman

Cayla Erdman

1594369800

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Models for SQL exist. In any case, the SQL that can be utilized on every last one of the major RDBMS today is in various flavors. This is because of two reasons:

1. The SQL order standard is genuinely intricate, and it isn’t handy to actualize the whole standard.

2. Every database seller needs an approach to separate its item from others.

Right now, contrasts are noted where fitting.

#programming books #beginning sql pdf #commands sql #download free sql full book pdf #introduction to sql pdf #introduction to sql ppt #introduction to sql #practical sql pdf #sql commands pdf with examples free download #sql commands #sql free bool download #sql guide #sql language #sql pdf #sql ppt #sql programming language #sql tutorial for beginners #sql tutorial pdf #sql #structured query language pdf #structured query language ppt #structured query language

Ray  Patel

Ray Patel

1625843760

Python Packages in SQL Server – Get Started with SQL Server Machine Learning Services

Introduction

When installing Machine Learning Services in SQL Server by default few Python Packages are installed. In this article, we will have a look on how to get those installed python package information.

Python Packages

When we choose Python as Machine Learning Service during installation, the following packages are installed in SQL Server,

  • revoscalepy – This Microsoft Python package is used for remote compute contexts, streaming, parallel execution of rx functions for data import and transformation, modeling, visualization, and analysis.
  • microsoftml – This is another Microsoft Python package which adds machine learning algorithms in Python.
  • Anaconda 4.2 – Anaconda is an opensource Python package

#machine learning #sql server #executing python in sql server #machine learning using python #machine learning with sql server #ml in sql server using python #python in sql server ml #python packages #python packages for machine learning services #sql server machine learning services

Brain  Crist

Brain Crist

1600347600

SCHEMAS in SQL Server -MS SQL Server – Zero to Hero Query Master

Introduction

This is part 3 of “MS SQL Server- Zero to Hero” and in this article, we will be discussing about the SCHEMAS in SQL SERVER. Before getting into this article, please consider to visit previous articles in this series from below,

A glimpse of previous articles
Part 1

In part one, we learned the basics of data, database, database management system, and types of DBMS and SQL.

Part 2
  • We learned to create a database and maintain it using SQL statements.
  • Best practice methods were also mentioned.

#sql server #benefits of schemas #create schema in sql #database schemas #how to create schema in sql server #schemas #schemas in sql server #sql server schemas #what is schema in sql server

Cayla  Erdman

Cayla Erdman

1596441660

Welcome Back the T-SQL Debugger with SQL Complete – SQL Debugger

When you develop large chunks of T-SQL code with the help of the SQL Server Management Studio tool, it is essential to test the “Live” behavior of your code by making sure that each small piece of code works fine and being able to allocate any error message that may cause a failure within that code.

The easiest way to perform that would be to use the T-SQL debugger feature, which used to be built-in over the SQL Server Management Studio tool. But since the T-SQL debugger feature was removed completely from SQL Server Management Studio 18 and later editions, we need a replacement for that feature. This is because we cannot keep using the old versions of SSMS just to support the T-SQL Debugger feature without “enjoying” the new features and bug fixes that are released in the new SSMS versions.

If you plan to wait for SSMS to bring back the T-SQL Debugger feature, vote in the Put Debugger back into SSMS 18 to ask Microsoft to reintroduce it.

As for me, I searched for an alternative tool for a T-SQL Debugger SSMS built-in feature and found that Devart company rolled out a new T-SQL Debugger feature to version 6.4 of SQL – Complete tool. SQL Complete is an add-in for Visual Studio and SSMS that offers scripts autocompletion capabilities, which help develop and debug your SQL database project.

The SQL Debugger feature of SQL Complete allows you to check the execution of your scripts, procedures, functions, and triggers step by step by adding breakpoints to the lines where you plan to start, suspend, evaluate, step through, and then to continue the execution of your script.

You can download SQL Complete from the dbForge Download page and install it on your machine using a straight-forward installation wizard. The wizard will ask you to specify the installation path for the SQL Complete tool and the versions of SSMS and Visual Studio that you plan to install the SQL Complete on, as an add-in, from the versions that are installed on your machine, as shown below:

Once SQL Complete is fully installed on your machine, the dbForge SQL Complete installation wizard will notify you of whether the installation was completed successfully or the wizard faced any specific issue that you can troubleshoot and fix easily. If there are no issues, the wizard will provide you with an option to open the SSMS tool and start using the SQL Complete tool, as displayed below:

When you open SSMS, you will see a new “Debug” tools menu, under which you can navigate the SQL Debugger feature options. Besides, you will see a list of icons that will be used to control the debug mode of the T-SQL query at the leftmost side of the SSMS tool. If you cannot see the list, you can go to View -> Toolbars -> Debugger to make these icons visible.

During the debugging session, the SQL Debugger icons will be as follows:

The functionality of these icons within the SQL Debugger can be summarized as:

  • Adding Breakpoints to control the execution pause of the T-SQL script at a specific statement allows you to check the debugging information of the T-SQL statements such as the values for the parameters and the variables.
  • Step Into is “navigate” through the script statements one by one, allowing you to check how each statement behaves.
  • Step Over is “execute” a specific stored procedure if you are sure that it contains no error.
  • Step Out is “return” from the stored procedure, function, or trigger to the main debugging window.
  • Continue executing the script until reaching the next breakpoint.
  • Stop Debugging is “terminate” the debugging session.
  • Restart “stop and start” the current debugging session.

#sql server #sql #sql debugger #sql server #sql server stored procedure #ssms #t-sql queries

Karlee  Will

Karlee Will

1620516240

Key Differences between SQL Server 2019 for Windows and Linux

Since the release of SQL Server 2017 for Linux, Microsoft has pretty much changed the entire game. It enabled a whole new world of possibilities for their famous relational database, offering what was only available in the Windows space until then.

I know that a purist DBA would tell me right away that the out of the box SQL Server 2019 Linux version has several differences, in terms of features, in regards to its Windows counterpart, such as:

  • No SQL Server Agent
  • No FileStream
  • No System Extended Stored Procedures (e.g. xp_cmdshell)

However, I got curious enough to think “what if they can be compared, at least to some extent, against things that both can do?” So, I pulled the trigger on a couple of VMs, prepared some simple tests, and collected data to present to you. Let’s see how things turn out!

#sql server #sql server 2019 #sql server linux #sql server windows #sql