MS-SQL - Send Mail from SQL Server 2008

In this article i will show you how to configure and send mail through SQL Server 2008.

Database Mail was introduced in SQL Server 2005 and we can say it is a complete replacement of SQL Mail of SQL Server earlier version. Database Mail is designed for reliability, scalability, security, and supportability.

Advantages of using Database Mail.

• It's easy to configure, fast and reliable.
• Auditing and Maintaining Logs of every mail.
• Can send HTML messages, attachments,Attach Query ResultSet as TextFile.

Let See How to configure Database mail in SQL server 2008.

Step 1

We need to create a profile and account using the Configure Database Mail Wizard.This wizard is used to manage accounts, profiles, and Database Mail global settings.

Open Management Nod, right click on Database Mail and click Configure Database Mail.it is look like this

Click on Image for better View

Step 2

Database Mail Wizard will Open,it is look like this

Click on Image for better View

Step 3

Setup Database Mail,Click Next Button,it is look like this

Click on Image for better View

Step 4

Add profile name and profile description and click Add button,it is look like this

Click on Image for better View

Step 5

Enter Database Mail Account details and click OK button,it is look like this

Click on Image for better View

All the information needed for SQL Server to send an email to a Simple Mail Transfer Protocol (SMTP) server.

• Account Name: Name that will be used to identify the database mail account.

• Description: Optional description used to identify the database mail account.

• E-mail Address: Email address that the mail is being sent from.

• Display Name: Optional name that is displayed on email messages to indicate who the email is from.

• Reply E-mail: Optional email address that will be used when someone replies to an email sent by this account.

• Server Name: Server name or IP address of the SMTP server that is used to send emails for this account.

• Port Number: Port number that is used to connect to the SMTP server.

• This Server Requires a Secure Connection (SSL): This option will encrypt communication between SQL Server and the SMTP server. You must have a certificate installed for SQL Server in order to use this option.

• Windows Authentication Using Database Engine Service Credentials: This option will use the MSSQLServer service credentials to connect to the SMTP server.

• Basic Authentication: This option allows you to specify a username and password in order to connect to the SMTP server.

• Anonymous Authentication: This option connects to SMTP servers without passing any login credentials and is used for SMTP servers that do not require authentication.

Step 6

Now New Account Profile Create,Click Next Button,it is look like this

Click on Image for better View

Step 7

Manage Profile Security, Click Next Button

Click on Image for better View

We can configure Database Mail profiles as public profiles or private profiles. If you configure the profile as public, anyone who can access the msdb database is allowed to use the profile to send mail. If you configure the profile as private, the profile will be limited to only specific users. You can also have one default public profile and one default private profile. A default profile allows you to send mail without specifying a profile name. When an account has access to both a default private profile and a default public profile, the default private profile will be used. If you would like to make the profile public, select the Public check box next to the profile name; if you would like to make the profile private, select the Private Profiles tab

Note: No need to select any CheckBox just Click Next button.

Step 8

Configure System Parameter,Click Next Button,it is look like this

Click on Image for better View

We can use the Configure System Parameters screen to define the system parameters for an entire instance of SQL Server; any changes you make here will apply to all Database Mail profiles and accounts. 

• Account Retry Attempts: Number of times Database Mail will retry to send mail using each account in a profile. For example, if you set the Account Retry Attempts parameter to three and you have two accounts in a profile, each account will retry to send mail three times.

• Account Retry Delay (Seconds): Number of seconds Database Mail will wait between retries. This delay is not the delay between each account in a profile. Database Mail will attempt to use all accounts in a profile, and then wait the defined number of seconds before trying all accounts again.

• Maximum File Size (Bytes): Limits the size of an attachment.

• Prohibited Attachment File Extensions: Comma-delimited list of file extensions that are not permitted as attachments when sending Database Mail.

• Database Mail Executable Minimum Lifetime (Seconds): The minimum time the external mail process will remain active even if there are no items in the mail queue. If items are in the queue, the external mail process will remain active until all items are processed.

• Logging Level: There are three logging levels you can use to send events to the Database Mail log: Normal, Extended, and Verbose. The Normal logging level will log errors. The Extended logging level will log errors, warnings, and informational messages. (Extended is the default logging level.) The Verbose logging level will log errors, warnings, informational messages, success messages, and internal messages. You should generally only use the Verbose logging level for troubleshooting purposes.

Step 9

Complete the Wizard,Click Finish Button,it is look like this

Click on Image for better View

Click on Image for better View

Click on Close Button.

Step 10

Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure

USE msdb
GO
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Step 11
All configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters.

1. Send Basic Email

-- Send Basic Email
EXEC sp_send_dbmail @profile_name='KishorNaik',
@recipients='kishor.naik011.net@gmail.com',
@subject='Test message from Sql Server',
@body='Sending mail from SQL Server 2008'

Output

Click on Image for better View

2. Send Select Query ResultSet

-- Send Select Query ResultSet
EXEC sp_send_dbmail @profile_name='KishorNaik',
@recipients='kishor.naik011.net@gmail.com',
@subject='Query Result Set',
@body='Select Query Result Set',
@query='SELECT Northwind..Employees.FirstName,Northwind..Employees.LastName FROM Northwind..Employees
'

Output

Click on Image for better View

3. Send mail attach the Select Query Resultset as Text File

-- Send mail attach the Select Query Resultset as Text File.
EXEC sp_send_dbmail @profile_name='KishorNaik',
@recipients='kishor.naik011.net@gmail.com',
@subject='Attach the Result as Text File',
@body='Find the attachment.',
@query='SELECT Northwind..Employees.FirstName,Northwind..Employees.LastName FROM Northwind..Employees
',
@attach_query_result_as_file = 1,
@query_attachment_filename ='EmployeeResults.txt'

Output

Click on Image for better View

4. Send mail with Attachment

-- Send mail with Attachment
EXEC sp_send_dbmail @profile_name='KishorNaik',
@recipients='kishor.naik011.net@gmail.com',
@subject='Send Mail with Attachment',
@body='Find the attachment.',
@file_attachments ='D:\Koala.jpg'

Output

Click on Image for better View

5. Send mail that uses HTML Formatting

--Send mail that uses HTML Formatting
--Send mail that uses HTML Formatting
DECLARE @HtmlBody Nvarchar(MAX)

 SET @HtmlBody='<table border=1 cellpadding="5" cellspacing="5">'
 SET @HtmlBody=@HtmlBody+'<tr>'
 SET @HtmlBody=@HtmlBody+'<th>First Name</th>'
 SET @HtmlBody=@HtmlBody+'<th>Last Name</th>'
 SET @HtmlBody=@HtmlBody+'</tr>'
 
 SELECT @HtmlBody=@HtmlBody+'<tr><td>'+Northwind..Employees.FirstName+
     '</td>'+'<td>'+Northwind..Employees.LastName+'</td></tr>' 
     FROM Northwind..Employees
     
 SET @HtmlBody=@HtmlBody+'</table>'

EXEC sp_send_dbmail @profile_name='KishorNaik',
@recipients='kishor.naik011.net@gmail.com',
@subject='HTML Formatting',
@body=@HtmlBody,
@body_format = 'HTML'

Output

Click on Image for better View

Step 11
Status of mail sent,it is look like this 

SELECT * FROM sysmail_mailitems

Output

Click on Image for better View

Step 12
Mail Logs

SELECT * FROM sysmail_log

Output

Click on Image for better View

Download
Scripts

WPF - ProgressBar in DataGrid Column in WPF

In this article i will show you how to add Progressbar in DataGrid Column in WPF 4. 

Step 1
Download Northwind database from the following link.
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

Step 2
Attach a Northwind database into MS-SQL server.

Step 3
Create a WPF Application and give solution name as SolProgressBarinDataGrid.

Step 4
Create a New Folder in Solution and give the Folder Name as ORD,it is look like this

Click on Image for Better View

Step 5
Add a Linq to Sql class,Select the ORD folder,right click on Add new Item,select LINQ to SQL classes from installed Visual Studio templates and name it NorthwindDC and click on add button,it is look like this

Click on Image for Better View

Step 6

Open a O/R Designer by double click on NorthwindDC.dbml,it is look like this

Click on Image for Better View

Click on Image for Better View

Visual studio provides an object-relational mapping designer,called the O/R Designer which allows you to visually design the object to database mapping.

Step 7

Create a Employee and Order object that will use LINQ to SQL to map to this table.go to the Server Explorer,select Northwind database,go to the Tables and select Employees and Orders table,it is look like this

Click on Image for Better View

Drag and drop Employees and Orders table from Server explorer onto the design surface of the O/R Designer,it is look like this

Click on Image for Better View

Step 8

Create a OrderCount Static Class in Solution for retrieving total number of Count Orders of each Employee Using Linq or Lambda Expression.it is look like this 

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SolProgressBarinDataGrid
{
    public static class OrderCount
    {
        #region Methods

        /// <summary>
        /// Get Order Count Data of Each Employee
        /// </summary>
        /// <returns>IList</returns>
        public static IList GetOrderCountData()
        {
            try
            {
                // Create Instance of Northwind DataContext 
                ORD.NorthwindDCDataContext DC = new ORD.NorthwindDCDataContext();

                // Using Linq Query
                var Query = (from E in DC.Employees
                             join O in DC.Orders
                             on E.EmployeeID equals O.EmployeeID
                             into EmployeeOrder
                             orderby E.FirstName ascending
                             select new
                             {
                                 EmployeeName = E.FirstName + " " + E.LastName,
                                 OrderCounts = EmployeeOrder.Count()
                             }
                               );

                // Using Lambda Expression
                //var Query = (DC.Employees.GroupJoin(DC.Orders,
                //              Employee => Employee.EmployeeID,
                //              Order => Order.EmployeeID,
                //              (Employee, EmployeeGroup) => new
                //              {
                //                  EmployeeName = Employee.FirstName + " " + Employee.LastName,
                //                  OrderCounts = EmployeeGroup.Count()
                //              }

                //              ));

                return Query.ToList();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message); 
            }
        }

        #endregion
    }
}

Output of Result Using LinqPad

Click on Image for Better View

Download LinqPad
http://www.linqpad.net/

Step 9
Now add DataGrid Control on window,it is look like this

<Grid>
  
      <DataGrid x:Name="DgOrderCount" Grid.Row="0" Grid.Column="0" AutoGenerateColumns="False" CanUserAddRows="False" ItemsSource="{Binding}">

      </DataGrid>
  
</Grid>

Step 10
Select DataGrid Control and Add DataGridTextColumn in DataGrid where we bind Employee Full Name,it is look like this

<DataGrid x:Name="DgOrderCount" Grid.Row="0" Grid.Column="0" AutoGenerateColumns="False" CanUserAddRows="False" ItemsSource="{Binding}">
   <DataGrid.Columns>
    <DataGridTextColumn Header="Employee Name"  Binding="{Binding EmployeeName}" Width="120"/>
                        </DataGrid.Columns>
  </DataGrid>
  
 </Grid>

Click on Image for Better View

Step 11

Select DataGrid and Add DataGridTemplateColumn in DataGrid where we add ProgreeBar control in Column and Bind Order Count value to Progressbar control and TextBlock control,it is look like this

<DataGrid x:Name="DgOrderCount" Grid.Row="0" Grid.Column="0" AutoGenerateColumns="False" CanUserAddRows="False" ItemsSource="{Binding}">
   <DataGrid.Columns>
    <DataGridTextColumn Header="Employee Name"  Binding="{Binding EmployeeName}" Width="120"/>
    <DataGridTemplateColumn Header="Orders Count" Width="380">
     <DataGridTemplateColumn.CellTemplate>
      <DataTemplate>
       <Grid>
        <ProgressBar Grid.Row="0" Grid.Column="0"  Minimum="0" Maximum="200" Value="{Binding OrderCounts,Mode=OneWay}" ToolTip="{Binding OrderCounts,Mode=OneWay}">
        </ProgressBar>
       <TextBlock Grid.Row="0" Grid.Column="0" Text="{Binding OrderCounts,Mode=OneWay}" HorizontalAlignment="Center" VerticalAlignment="Center"></TextBlock>
       </Grid>
      </DataTemplate>
     </DataGridTemplateColumn.CellTemplate>
    </DataGridTemplateColumn>
   </DataGrid.Columns>
  </DataGrid>

Click on Image for Better View

Full Code of XAML

<Window x:Class="SolProgressBarinDataGrid.MainWindow"
  xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
  Title="Progressbar in DataGrid Column" Height="350" Width="525" Loaded="Window_Loaded_1">
 
 <Grid>
  
  <DataGrid x:Name="DgOrderCount" Grid.Row="0" Grid.Column="0" AutoGenerateColumns="False" CanUserAddRows="False" ItemsSource="{Binding}">
   <DataGrid.Columns>
    <DataGridTextColumn Header="Employee Name"  Binding="{Binding EmployeeName}" Width="120"/>
    <DataGridTemplateColumn Header="Orders Count" Width="380">
     <DataGridTemplateColumn.CellTemplate>
      <DataTemplate>
       <Grid>
        <ProgressBar Grid.Row="0" Grid.Column="0"  Minimum="0" Maximum="200" Value="{Binding OrderCounts,Mode=OneWay}" ToolTip="{Binding OrderCounts,Mode=OneWay}">
        </ProgressBar>
       <TextBlock Grid.Row="0" Grid.Column="0" Text="{Binding OrderCounts,Mode=OneWay}" HorizontalAlignment="Center" VerticalAlignment="Center"></TextBlock>
       </Grid>
      </DataTemplate>
     </DataGridTemplateColumn.CellTemplate>
    </DataGridTemplateColumn>
   </DataGrid.Columns>
  </DataGrid>
  
 </Grid>
</Window>

Step 12
Now Bind the data in DataGrid Control in Code Behind on Window Load Event,it is look like this

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace SolProgressBarinDataGrid
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();

           
        }

        private void Window_Loaded_1(object sender, RoutedEventArgs e)
        {
            try
            {
                // Get Order Count Data In IList
                IList OrderCountList = OrderCount.GetOrderCountData();

                // Check the IList is Null or Not
                if (OrderCountList != null)
                {
                    // Check the IList Count is Grether than 0 or not
                    if (OrderCountList.Count > 0)
                    {
                        // Bind Data to DataGrid Control
                        DgOrderCount.DataContext = OrderCount.GetOrderCountData();
                    }
                }
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message); 
            }
        }
    }
}

Output

Click on Image for Better View

Download
Download Source Code