Configuring Email in SQL Server Express

Its a known issue that SQL Server express edition wont support Email . TO overcome this we can use CLR based email stored procedure.

 

Step 1 :

Compile the VB code and convert as a dll.

Code :

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("smtp.zxvzxvxzvx.com")
mySmtpClient.Credentials = New System.Net.NetworkCredential("[email protected]", "vzxvxzvxzvz")
mySmtpClient.Send(myMessage)

End Using
End Sub
End Class

Step 2:

Replace these 2 lines with your smtp username , password amd smtp server adress

mySmtpClient = New SmtpClient(“smtpxxxxxxxx.com”)
mySmtpClient.Credentials = New System.Net.NetworkCredential(“[email protected]”, “Password”)

Save the above file in d:\sendmail.vb

Step 3:

Complie the code using below command.

 

C:\Windows\system32>C:\Windows\Microsoft.NET\Framework\v4.0.30319\vbc /target:li brary D:\sendmail.vb

The above will comile and create a file sendmail.dll in the same location.

Step 4:

Run this command in SQL management studio

ALTER DATABASE msdb SET trustworthy ON

Step 5:

Create the assembly in SQL management studio

 

USE msdb 
GO 

CREATE ASSEMBLY SendEmail FROM 'C:\SendEmail.dll' 
WITH PERMISSION_SET = UNSAFE 
GO 

Step 6:

Enable CLR

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Step 7:

Create stored procedure

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]

Step 8

Testing

 

EXEC spSendMail @recipients = '[email protected]', @subject = 'Email from SQL Express', 
@from = '[email protected]', @body = 'This is a test email using Haneef Code'

If you want to send mails to multiple users use the email id as comma separated.

 

Good Luck