Send Email from SQL Server Express Using a CLR Stored Procedure.

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.


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.


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 String, ByVal subject As String, ByVal from As String, ByVal body As String) 
        Dim mySmtpClient As SmtpClient 

        Using myMessage As New MailMessage(from, recipients) 

            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'


Step 5 - Cleanup 

If you want to remove these objects follow these steps. Delete the vb file and dll. And run the sql statements given below.

USE msdb 

GO 

DROP PROCEDURE dbo.spSendMail 

GO  

DROP ASSEMBLY SendEmail 

GO 

Note: I got this article from one of the site, but i was worked on this and its working fine.