Learn more about SQL Server tools


Latest from MSSQLTips

Send Email from SQL Server Express Using a CLR Stored Procedure

MSSQLTips author Greg Robidoux By:   |   Read Comments (28)   |   Related Tips: More > Express Edition

One of the nice things about SQL Server is the ability to send email using T-SQL.  The downside is that this functionality does not exist in SQL Server Express.  In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

CLR Functions - Getting started with a string sort function

In this CLR stored procedure we are going to pass in a few parameters such as:

  • recipients - list of people to receive the email
  • subject - subject line of the email
  • from - who the email is from
  • body - the body of the email

This is just the basic information to send an email.  You could add as many parameters as you want to extend the functionality, but I kept it simple to illustrate how easy this is to do.

The code in this tip was taken from Sending HTML formatted email using SSIS Script Task.

Step 1 - CLR code

The first thing we need to do is to write the CLR code.  This could be written in either C#.NET or VB.NET.  In this example we are using VB.NET.

The following code has a Class (StoredProcedure) and a Stored Procedure (spSendMail).  The stored procedure takes four parameters:

  • recipients - list of people to receive the email
  • subject - subject line of the email
  • from - who the email is from
  • body - the body of the email

Before you save the code you will need to change these two lines to include the data for your SMTP server and your account you will use to send emails:

  • mySmtpClient = New SmtpClient("mail.yourservername.com") -- this should be your SMTP server
  • mySmtpClient.Credentials = New NetworkCredential("email@domain", "yourPassword") -- this should the email address and password to authenticate in to your email server

After you have made these two adjustments save the code below in a file called:  C:\SendEmail.vb

Imports System.Net
Imports System.Net.Mail

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

        Using myMessage 
As New MailMessage(fromrecipients)

myMessage.Subject subject

= True

mySmtpClient = New SmtpClient("mail.yourservername.com")
mySmtpClient.Credentials = New NetworkCredential("email@domain""yourPassword")

End Using
End Sub


Step 2 - Compile CLR Code

In order to use this code, the code has to be compiled. 

The following command is run from a command line to compile the CLR code using the vbc.exe application.  This is found in the .NET 2.0 framework directory.  This may be different on your server or desktop.  Also, this code should be compiled on the machine where the code will run.

So from a command line run a command such as the following:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc /target:library C:\SendEmail.vb

The code should now be compiled in a file called: C:\SendEmail.dll


Step 3 - Create Assembly and Stored Procedure

After the code has been compiled you need to create the assembly and the stored procedure within SQL Server.  To do this, run these commands in the database where you want to use the function.  For this example I am using the msdb database.

The assembly ties an internal object to the external DLL that was created and the stored procedure is similar to a normal SQL Server stored procedure.

For the stored procedure you will see the components that are referenced [SendEmail].[SendEmail.StoredProcedure].[spSendMail]

USE msdb

CREATE ASSEMBLY SendEmail FROM 'C:\SendEmail.dll'  

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

If you get error messages when trying to compile the code you may need to alter the database using the following command and then try again to create the assembly and the stored procedure.

ALTER DATABASE msdb SET trustworthy ON


Step 4 - Test It

To test the stored procedure, run the following statement based on the values you want to pass.

In this example we are sending an email to "tips@mssqltips.com".

EXEC spSendMail @recipients 'tips@mssqltips.com'@subject 'Email from SQL Express'
'tips@mssqltips.com'@body 'This is a test email from SQL Server'

In this example we are sending an email to "tips@mssqltips.com" and "joe@edgewoodsolutions.com".

EXEC spSendMail @recipients 'tips@mssqltips.com, joe@edgewoodsolutions.com'
'Email two from SQL Express'
@from 'tips@mssqltips.com'@body 'This is a test email from SQL Server'


Step 5 - Cleanup

If you want to remove these objects follow these steps.

  • Delete the DLL that was created
  • Delete the VB file that was created
  • Run these T-SQL statements
USE msdb



That's all there is to creating a CLR stored procedure to send email. This was a basic example and only offered a few options, but you can easily extend this to add other features.


Next Steps

Last Update: 7/16/2009

About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources

print tip Print  
Become a paid author

Learn more about SQL Server tools

Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Tuesday, May 26, 2015 - 9:19:40 PM - Chris Read The Tip

Hi Greg, sorry to dredge up an old one! Thank you so much for this tip, it works beautifully. Can you advise how I might be able to get a new line into the body text of the email. I am currently declaring an nvarchar variable for the body text and concatenating various other string variable values, but ultimately I would like to seperate them on new lines. I've tried CHAR(10) and CHAR(13) in the variable set statment, but no luck at this stage.



Wednesday, August 14, 2013 - 6:00:11 PM - Assaf Read The Tip


I have a problem :


Msg 6522, Level 16, State 1, Procedure spSendMail, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "spSendMail": 

System.Net.Mail.SmtpException: The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.0 Must issue a STARTTLS command first. p5sm78843246eeg.5 - gsmtp


   at System.Net.Mail.MailCommand.CheckResponse(SmtpStatusCode statusCode, String response)

   at System.Net.Mail.SmtpTransport.SendMail(MailAddress sender, MailAddressCollection recipients, String deliveryNotify, SmtpFailedRecipientException& exception)

   at System.Net.Mail.SmtpClient.Send(MailMessage message)

   at StoredProcedure.spSendMail(String recipients, String subject, String from, String body)


Can you assist please?

Tuesday, July 16, 2013 - 1:23:49 AM - joseph Read The Tip

Great TIP sir! Thanks a lot.

Tuesday, March 05, 2013 - 12:37:17 PM - Jimmy Read The Tip

Hello, I have this problem with the script at the time of the test:

Msg 6522, Level 16, State 1, Procedure spSendMail, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "spSendMail":

System.Net.Mail.SmtpException: Se excedi el tiempo de espera de la operacin.


en System.Net.Mail.SmtpClient.Send(MailMessage message)

en StoredProcedure.spSendMail(String recipients, String subject, String from, String body)

For reference, here in the company we use Microsoft Exchange as mail server.

Monday, February 04, 2013 - 2:23:00 AM - James Read The Tip

Problem resolved by recreating the assembly in SQL Server Express after inserting the new port setting in SendMail.dll.

Thanks for your great program example...

Monday, February 04, 2013 - 1:00:43 AM - James Read The Tip

Hi Greg,

I have tested your program code but encountered the following error when executing the stored procedure.

System.Net.Mail.SmtpException: Failure sending mail. ---> System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: 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

Using the telnet to confirm the server host and port number. Response received when change to port 587. Possibly port 25 is blocked by the ISP.
Added in the below line to change the default port number to 587 in the SendMail.dll.
mySmtpClient.Port = 587
But still encountered same error as above which showed but not 587 even I have inserted the port setting in the dll file.
Need your advice. Thanks.

Friday, November 30, 2012 - 8:47:16 AM - Marcos Read The Tip

Hi Greg,

Now that I can send emails using SQLServer, I would like to know if is possible create a similar CLR store procedure to receive emails. Do you think is it possible?



Wednesday, November 28, 2012 - 11:56:04 AM - Neil Read The Tip

Is there a way to set the @body variable to a select statement against one of the tables in the database that this procedure and assembly live?


Thanks in advance!

Monday, September 10, 2012 - 8:53:24 AM - Greg Robidoux Read The Tip

@Marcos - yes this can be done.  Take a look at this post: http://msmvps.com/blogs/siva/archive/2008/03/14/sending-emails-with-delivery-receipt-notification.aspx


These items would just need to be place within the code.

Friday, September 07, 2012 - 10:25:44 AM - Marcos Read The Tip

Hi Greg,

This tip is perfect. Thank you.

But I would like know if is possible to have a return with the sending message result, I mean, if the message was send successfull or not.

Thank again.


Tuesday, July 17, 2012 - 9:58:02 AM - Greg Robidoux Read The Tip

Blas - try to use an internal email to see if this works.  It looks like your SMTP server does not allow relaying to send emails outside of the domain.

You could also take a look at this tip to see if using different SMTP settings might work for you: http://www.mssqltips.com/sqlservertip/2578/setup-sql-server-database-mail-to-use-a-gmail-hotmail-yahoo-or-aol-account/

Monday, July 16, 2012 - 5:40:44 PM - Blas Read The Tip

hello, Can you help me with this error

Mens 6522, Level 16, State 1, Procedure spSendMail, Line 0 Error. NET Framework error occurred during the execution of the routine or aggregate user-defined 'spSendMail': System.Net.Mail.SmtpFailedRecipientException: Mailbox unavailable. The server response was: 5.7.1 Unable to relay for d4n1el.blas @ gmail.com System.Net.Mail.SmtpFailedRecipientException:    at System.Net.Mail.SmtpTransport.SendMail (MailAddress sender, recipients MailAddressCollection, String deliveryNotify, SmtpFailedRecipientException & exception)    at System.Net.Mail.SmtpClient.Send (MailMessage message)    at StoredProcedure.spSendMail (String recipients, String subject, String from, String body)



Thursday, April 05, 2012 - 8:57:00 PM - David Read The Tip

Sinan, you should be able to use this technique to send email from any server that you have access to via smtp.  Just configure the connection properly http://lifehacker.com/111166/how-to-use-gmail-as-your-smtp-server

Tuesday, April 03, 2012 - 1:44:28 PM - David Read The Tip

I am in the process of migrating my server from SQL Express 2005 on a Windows 2003 Server to SQL Express 2008 on a Windows 2008 64-bit server.  I just deplyed my CLR procedure to send mail and it works on the new server, but it is very slow.  It is taking 9 seconds to execute and send the email whereas it was instantaneos on my old database/server.  Anybody have any idea what may be slowing it down?

Tuesday, April 03, 2012 - 7:25:09 AM - Sinan Read The Tip


thanks alot greg i think my answer should be here since  Sql server express  does not have the option for databasemail :(

Monday, April 02, 2012 - 7:48:53 PM - Greg Robidoux Read The Tip

Sinan, take a look at this tip: http://www.mssqltips.com/sqlservertip/2578/setup-sql-server-database-mail-to-use-a-gmail-hotmail-yahoo-or-aol-account/

Monday, April 02, 2012 - 10:57:29 AM - Sinan Read The Tip


Hi Greg

i unfortunately have sql server express but no email server so i would like to use my gmail to send mails

would it be possible for me to configure this code to be used with gmail or should i look into some other mathod

Thursday, March 29, 2012 - 7:18:52 PM - David Read The Tip

Greg, thanks for this post it really came in handy.  I was able to implement a stored procedure and a trigger that automates the sending of order shipment notification emails for my website with this.  Great job, and thanks again!

Wednesday, December 14, 2011 - 10:27:57 AM - molly Read The Tip


Thanks for the mail sending script... ............. Amazing really

Friday, May 13, 2011 - 5:40:21 AM - Greg Robidoux Read The Tip

I don't think you can do it with either of those email services.  You need to know the server info for the SMTP server.

Friday, May 13, 2011 - 3:34:07 AM - sqlchild Read The Tip

how do i know the correct server and or IP address for my mail server. i use gmail and rediffmail.

Wednesday, May 11, 2011 - 9:46:32 PM - Bob Read The Tip

Just wanted to say THANKS to Greg Robidoux!

This is exactly what I needed for a problem I needed to solve.

The solution was well documented, easy to follow and best of all did the


Thanks again,


Thursday, February 17, 2011 - 2:50:09 PM - Greg Robidoux Read The Tip

Hi sqlchild,

Are you sure you entered the correct server and or IP address for your mail server?

Wednesday, February 16, 2011 - 5:29:53 AM - Clara Read The Tip

And how about sending a query result in the body of this message? Is it possible with SQL Server Management Studio Express? How?

Thanks in advance,

Monday, February 07, 2011 - 1:23:42 AM - sqlchild Read The Tip



Msg 6522, Level 16, State 1, Procedure spSendMail, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "spSendMail": 

System.Net.Mail.SmtpException: Failure sending mail. ---> System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it


   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)

   at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)

   at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)


   at System.Net.ServicePoint.GetConnection(PooledStream PooledStream, Object owner, Boolean async, IPAddress& address, Socket& abortSocket, Socket& abortSocket6, Int32 timeout)

   at System.Net.PooledStream.Activate(Object owningObject, Boolean async, Int32 timeout, GeneralAsyncDelegate asyncCallback)

   at System.Net.PooledStream.Activate(Object owningObject, GeneralAsyncDelegate asyncCallback)

   at System.Net.ConnectionPool.GetConnection(Object owningObject, GeneralAsyncDelegate asyncCallback, Int32 creationTimeout)

   at System.Net.Mail.SmtpConnection.GetConnection(String host, Int32 port)

   at System.Net.Mail.SmtpTransport.GetConnection(String host, Int32 port)

   at System.Net.Mail.SmtpClient.GetConnection()

   at System.Net.Mail.SmtpClient.Send(MailMessage message)


   at System.Net.Mail.SmtpClient.Send(MailMessage message)

   at StoredProcedure.spSendMail(String recipients, String subject, String from, String body)

Wednesday, October 07, 2009 - 8:28:32 PM - Novice Read The Tip

Thanks. Email works now..


Tuesday, October 06, 2009 - 8:02:39 AM - admin Read The Tip

You can try this:

USE msdb


@recipients [nvarchar](4000
@subject [nvarchar](4000
@from [nvarchar](4000
@body [nvarchar](4000
EXTERNAL NAME [SendEmail].[StoredProcedure].[spSendMail]

Sunday, July 19, 2009 - 11:09:16 PM - Novice Read The Tip


This tip will be quite useful in our environment where we support multiple servers with sql express instances.

Having had no experience with .NET, I simply followed the instructions step by step. Even used cut and paste to make sure the names were in the correct case.

The following error was returned:

Msg 6505, Level 16, State 1, Procedure spSendMail, Line 3

Could not find Type 'SendEmail.StoredProcedure' in assembly 'SendEmail'.

Has anyone had this issue?


More SQL Server Solutions



Get Free SQL Tips










BI Professionals


Q and A

Today's Tip







Tip Categories

Search By TipID



First Timer?


Free T-shirt



User Groups

Author of the Year

More Info








Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.