Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Send Email from SQL Server Express Using a CLR Stored Procedure

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

Problem
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.

Solution
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
            myMessage.Body 
body

            myMessage.IsBodyHtml 
= True

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

        
End Using
    
End Sub
End 
Class

 

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
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].[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'
@from 
'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'
@subject 
'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
GO
DROP PROCEDURE dbo.spSendMail
GO 
DROP ASSEMBLY SendEmail
GO 

 

Summary

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





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Sunday, July 19, 2009 - 11:09:16 PM - Novice Read The Tip

Hi,

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?

Thanks.


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

You can try this:

USE msdb
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]


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

Thanks. Email works now..

 


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

DEAR SIR , 

I AM GETTING THE FOLLOWING ERROR:

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 64.136.45.145:25

System.Net.Sockets.SocketException: 

   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)

System.Net.WebException: 

   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)

System.Net.Mail.SmtpException: 

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

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


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,


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, 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

trick.

Thanks again,

..bob


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.


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.


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

 

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


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!


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


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/


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 :(


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?


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


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)

 

tnk


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/


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.

 


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.


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!


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?

Thanks.

 


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 202.75.35.162:25

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 202.75.35.162:25 but not 587 even I have inserted the port setting in the dll file.
 
Need your advice. Thanks.

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...


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.

System.Net.Mail.SmtpException:

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.


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

Great TIP sir! Thanks a lot.


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

Hi,

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

System.Net.Mail.SmtpException: 

   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?



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.