Download emails with SQLServer

In this blog post I’ll describe how to use Mail.dll .NET email component with SQL Server to download emails.

First, you need to create a regular .NET assembly with definition of SQL stored procedure. Remember to add reference to Mail.dll.

In this example the assembly name is SqlEmailDownloader.dll:

// C#

using System.Data;
using Microsoft.SqlServer.Server;
using Limilabs.Client.IMAP;
using Limilabs.Mail;

public class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetEmails()
    {
        SqlDataRecord record = new SqlDataRecord(new[]
             {
                 new SqlMetaData("UID", SqlDbType.BigInt),
                 new SqlMetaData("Subject", SqlDbType.NVarChar, 128)
             });

        using (Imap client = new Imap())
        {
            client.Connect("server"); // or ConnectSSL
            client.UseBestLogin("user", "password");
            client.SelectInbox();

            SqlContext.Pipe.SendResultsStart(record);
            foreach (long uid in client.Search(Flag.Unseen))
            {
                var eml = client.PeekHeadersByUID(uid);
                IMail email = new MailBuilder().CreateFromEml(eml);

                record.SetSqlInt64(0, uid);
                record.SetSqlString(1, email.Subject);
                SqlContext.Pipe.SendResultsRow(record);
            }
            client.Close();
            SqlContext.Pipe.SendResultsEnd();
        }
    }
} ;

The code above:

  • connects to the IMAP server,
  • finds all unseen emails,
  • downloads headers and
  • passes the results to SQL server.

Here’s the VB.NET version:

' VB.NET

Imports System.Data
Imports Microsoft.SqlServer.Server
Imports Limilabs.Client.IMAP
Imports Limilabs.Mail

Public Class StoredProcedures
	<microsoft.SqlServer.Server.SqlProcedure> _
	Public Shared Sub GetEmails()
		Dim record As New SqlDataRecord(New () { _
			New SqlMetaData("UID", SqlDbType.BigInt), _
			New SqlMetaData("Subject", SqlDbType.NVarChar, 128)})

		Using client As New Imap()
			client.Connect("server") ' or ConnectSSL
			client.UseBestLogin("user", "password")
			client.SelectInbox()

			SqlContext.Pipe.SendResultsStart(record)
			For Each uid As Long In client.Search(Flag.Unseen)
				Dim eml = client.PeekHeadersByUID(uid)
				Dim email As IMail = New MailBuilder() _
					.CreateFromEml(eml)

				record.SetSqlInt64(0, uid)
				record.SetSqlString(1, email.Subject)
				SqlContext.Pipe.SendResultsRow(record)
			Next
			client.Close()
			SqlContext.Pipe.SendResultsEnd()
		End Using
	End Sub
End Class

In SQL Server Management Studio you need to execute following script (HelloWorld is a sample database):

USE HelloWorld
GO

EXEC sp_configure 'clr enabled' , '1'
GO
RECONFIGURE
GO

Alter Database HelloWorld Set TrustWorthy On
GO

CREATE ASSEMBLY [System.Windows.Forms] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll'
With Permission_Set=Unsafe
GO
CREATE ASSEMBLY SqlEmailDownloaderAssembly FROM 'D:\1\SqlEmailDownloader\bin\Debug\SqlEmailDownloader.dll'
With Permission_Set=Unsafe
GO

CREATE PROCEDURE GetEmails
AS EXTERNAL NAME SqlEmailDownloaderAssembly.StoredProcedures.GetEmails
GO

Finally you can execute the procedure:

EXEC GetEmails

…and see the results:

Click here to download Mail.dll IMAP component

Tags:      

Questions?

Consider using our Q&A forum for asking questions.

10 Responses to “Download emails with SQLServer”

  1. Bill Stickers Says:
    October 4th, 2016 at 14:14

    I think it is pretty common to want to download emails and store them in a database. Is there a preferred way to do this for mail.dll or a recommended ADO model structure which corresponds to the IMail interface?

  2. Limilabs support Says:
    October 4th, 2016 at 18:51

    @Bill

    In our practice the most important thing is to save the original, raw eml data for later reference.

    True it takes space, but if you have raw data, at any point of time,
    you can check S/MIME digital signatures, DKIM signatures, and
    if you forgot to include some important detail in your DB structure,
    you are able to recreate it from original data.

    As for the DB structure we can’t give you any good advice,
    as it greatly depends on what are you planning to do with the data:
    Searching? Are attachments important? Do attachments need to be searchable?
    Are there any headers that are specially important to you?

  3. Mike Says:
    February 22nd, 2017 at 15:18

    Just having some problems. I have created my DLL based of yours but i get an error message when i run the sql query to create the stored procedure.
    Here is my code bellow

    Imports System.Data
Imports Microsoft.SqlServer.Server
Imports Limilabs.Client.IMAP
Imports Limilabs.Mail
Public Class Class1
        <Microsoft.SqlServer.Server.SqlProcedure> _
        Public Shared Sub GetEmails()
            Dim record As New SqlDataRecord({ _
                New SqlMetaData("UID", SqlDbType.BigInt), _
                New SqlMetaData("Subject", SqlDbType.NVarChar, 128)})

            Using client As New Imap()
                client.ConnectSSL("Imap.gmail.com")
                client.Login("username", "password")
                client.SelectInbox()

                SqlContext.Pipe.SendResultsStart(record)
                For Each uid As Long In client.Search(Flag.Unseen)
                    Dim eml = client.PeekHeadersByUID(uid)
                    Dim email As IMail = New MailBuilder() _
                    .CreateFromEml(eml)

                    record.SetSqlInt64(0, uid)
                    record.SetSqlString(1, email.Subject)
                    SqlContext.Pipe.SendResultsRow(record)
                Next
                client.Close()
                SqlContext.Pipe.SendResultsEnd()
            End Using
        End Sub
    End Class

    Here is the query

    USE HelloWorld
GO
EXEC sp_configure 'clr enabled' , '1'

GO

RECONFIGURE

GO
Alter Database HelloWorld Set TrustWorthy On

GO
CREATE ASSEMBLY [System.Windows.Forms] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll'
With Permission_Set=Unsafe

GO

CREATE ASSEMBLY SqlEmailDownloaderAssembly FROM 'E:\mail\SqlEmailDownloader.dll'

With Permission_Set=Unsafe

GO

CREATE PROCEDURE GetEmails

AS EXTERNAL NAME SqlEmailDownloaderAssembly.Class1.GetEmails

GO

    Thanks
    Mike

  4. Limilabs support Says:
    March 4th, 2017 at 13:46

    @Mike

    What error do you get? What is the exception message, stack trace?
    Can you enable logging?

  5. jesil james Says:
    April 17th, 2017 at 20:32

    I also get an error when i run the sql query ..the error is
    CREATE ASSEMBLY for assembly ‘Email_SQL’ failed because the assembly is built for an unsupported version of the Common Language Runtime

  6. jesil james Says:
    April 17th, 2017 at 20:59

    Sorry the above error was fixed which was a.net framwork issue. which i changed it to 3.5. Now when I am running the SP, I am getting the error -Could not find Type ‘StoredProcedures’ in assembly ‘SqlEmailDownloader’.

  7. jesil james Says:
    April 18th, 2017 at 19:28

    I am getting the Following error in SQL Server when I run the procedure

    Msg 6522, Level 16, State 1, Procedure GetEmails1, Line 0
    A .NET Framework error occurred during execution of user-defined routine or aggregate “GetEmails1”:
    Limilabs.Client.ServerException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 74.125.134.108:143 […]

  8. Limilabs support Says:
    April 19th, 2017 at 14:59

    @Jesil

    You simply can not connect. You are using incorrect server address or/and port number or your server is down. This connection attempt failed article may help you.

  9. Dushyant Says:
    April 9th, 2018 at 10:09

    In the stored procedure I am recieving the following error:

    Msg 6501, Level 16, State 7, Line 1
    CREATE ASSEMBLY failed because it could not open the physical file “D:\1\SqlEmailDownloader\bin\Debug\SqlEmailDownloader.dll”: 3(failed to retrieve text for this error. Reason: 15105).
    Msg 6528, Level 16, State 1, Procedure GetEmails, Line 2
    Assembly ‘SqlEmailDownloaderAssembly’ was not found in the SQL catalog of database ‘HelloWorld’.
    Msg 2812, Level 16, State 62, Line 2
    Could not find stored procedure ‘GetEmail’.

  10. Limilabs support Says:
    April 9th, 2018 at 12:20

    @Dushyant,

    Have you created this assembly, compiled it? Is the file in this folder. Does your SQL have enough permissions to read this file?