Haneef Puttur

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(“haneef@xcxzvcxzvz.com”, “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(“haneef@xxxxxxxx.com”, “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 […]