Sending E-Mail from SQL 2008 Express

  • APrisk2

    SSC Journeyman

    Points: 87

    Comments posted to this topic are about the item Sending E-Mail from SQL 2008 Express

  • Iulian -207023

    SSCertifiable

    Points: 7508

    Nice one Andrew. I can hardy wait to test it.

    Regards,

    Iulan

  • amet gbas

    SSC Journeyman

    Points: 83

    hi..

    tested it an get this error while trying to run stored procedure;

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

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

    System.Security.SecurityException: Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

    System.Security.SecurityException:

    at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

    at System.Security.CodeAccessPermission.Demand()

    at System.Net.Mail.SmtpClient.set_Port(Int32 value)

    at SQLCLREmail.SendEmail(String recipients, String CC, String BCC, String subject, String from, String body, String strAttachments, String strSMTPServer, String strSMTPServerPort, String strSMTPServerUser, String strSMTPServerPwd)

    .

  • blessonblessons

    SSC Eights!

    Points: 929

    Nice Article.

    We can create a linked server and access the msdb.dbo.sp_send_dbmail.

  • Anipaul

    SSC-Insane

    Points: 24681

    Good article... Nice

  • ashwin.nprabhu

    SSC Rookie

    Points: 27

    The article is very interesting, but just wanted to ask if the same thing can be explained using C# as reference for people like me.

  • AJN

    SSC Enthusiast

    Points: 195

    You have just introduced me to CLR...Thanks for a great article!!

  • Mike Is Here

    Hall of Fame

    Points: 3348

    Good Article. Two suggestions

    I would make the attribute appear in the same code window as the method. At first it looks confusing because you have

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    That ends in a continuation character that goes nowhere.

    Then maybe a little more explanation as to why this attribute is needed.

    Represents an abstraction of the caller's context, which provides access to the SqlPipe, SqlTriggerContext, and WindowsIdentity objects.

    or

    The SqlContext object can be used to obtain the trigger context (for code executing in a trigger) and the pipe object (when available, such as in stored procedures).

    Definitions are taken from the MSDN.

  • dmbaker

    SSCertifiable

    Points: 5024

    ashwin.nprabhu (11/4/2010)


    The article is very interesting, but just wanted to ask if the same thing can be explained using C# as reference for people like me.

    I don't think I would code it this way, but here's a more-or-less exact translation into C#. One issue is that the SMTP port is an integer, not a string. I'm not sure how that's working in VB.NET, guess is that VB implicitly casts it to the correct type. Don't know if you need namespace in a CLS proc either, but the code in the class should be "right".

    This is .NET 4.0, 2.0 or 3.5 should be pretty easy to translate into if you need to.

    using System;

    using System.Net;

    using System.Net.Mail;

    namespace YourNamespaceHere

    {

    public class SQLCLREmail

    {

    [Microsoft.SqlServer.Server.SqlProcedure]

    public void SendMail(string recipients, string CC, string BCC, string subject, string from, string body, string strAttachments, string strSMTPServer, int intSMTPServerPort, string strSMTPServerUser, string strSMTPServerPwd)

    {

    using (MailMessage MailMsg = new MailMessage())

    {

    MailMsg.From = new MailAddress(from);

    MailMsg.Subject = subject;

    MailMsg.Body = body;

    MailMsg.IsBodyHtml = true;

    if (!String.IsNullOrWhiteSpace(recipients))

    {

    string[] strTo = recipients.Split(';');

    foreach (string strRecip in strTo)

    {

    MailMsg.To.Add(new MailAddress(strRecip));

    }

    }

    if (!String.IsNullOrWhiteSpace(CC))

    {

    string[] strCCTo = CC.Split(';');

    foreach (string strCCRecip in strCCTo)

    {

    MailMsg.To.Add(new MailAddress(strCCRecip));

    }

    }

    if (!String.IsNullOrWhiteSpace(BCC))

    {

    string[] strBCCTo = BCC.Split(';');

    foreach (string strBCCRecip in strBCCTo)

    {

    MailMsg.To.Add(new MailAddress(strBCCRecip));

    }

    }

    if (!String.IsNullOrWhiteSpace(strAttachments))

    {

    string[] strAttach = strAttachments.Split(';');

    foreach (string strFile in strAttach)

    {

    MailMsg.Attachments.Add(new Attachment(strFile.Trim()));

    }

    }

    if (!String.IsNullOrWhiteSpace(strSMTPServer))

    {

    SmtpClient smtp = new SmtpClient();

    if (!String.IsNullOrWhiteSpace(strSMTPServerUser))

    {

    smtp.UseDefaultCredentials = false;

    smtp.Credentials = new NetworkCredential(strSMTPServerUser, strSMTPServerPwd);

    smtp.Host = strSMTPServer;

    smtp.Port = intSMTPServerPort;

    smtp.Send(MailMsg);

    }

    else

    {

    smtp.Host = "localhost";

    smtp.Port = 25;

    smtp.Send(MailMsg);

    }

    }

    }

    }

    }

    }

  • Jonathan manley

    SSC Enthusiast

    Points: 108

    Use the web based VB.NET to C# Converter:

    http://www.developerfusion.com/tools/convert/vb-to-csharp/

    Works pretty good, and gets most of this syntax converted.

  • APrisk2

    SSC Journeyman

    Points: 87

    using System.Net;

    using System.Net.Mail;

    public class SQLCLREmail

    {

    [Microsoft.SqlServer.Server.SqlProcedure()]

    public static void SendEmail(string recipients, string CC, string BCC, string subject, string @from, string body, string strAttachments, string strSMTPServer, string strSMTPServerPort, string strSMTPServerUser,

    string strSMTPServerPwd)

    {

    using (MailMessage MailMsg = new MailMessage()) {

    MailMsg.From = new MailAddress(@from);

    MailMsg.Subject = subject;

    MailMsg.Body = body;

    MailMsg.IsBodyHtml = true;

    if (!recipients.Equals(string.Empty)) {

    string strRecip = null;

    string[] strTo = recipients.Split(";");

    foreach ( strRecip in strTo) {

    MailMsg.To.Add(new MailAddress(strRecip));

    }

    }

    if (!CC.Equals(string.Empty)) {

    string strCCRecip = null;

    string[] strCCTo = CC.Split(";");

    foreach ( strCCRecip in strCCTo) {

    MailMsg.CC.Add(new MailAddress(strCCRecip));

    }

    }

    if (!BCC.Equals(string.Empty)) {

    string strBCCRecip = null;

    string[] strBCCTo = BCC.Split(";");

    foreach ( strBCCRecip in strBCCTo) {

    MailMsg.BCC.Add(new MailAddress(strBCCRecip));

    }

    }

    if (!strAttachments.Equals(string.Empty)) {

    string strFile = null;

    string[] strAttach = strAttachments.Split(";");

    foreach ( strFile in strAttach) {

    MailMsg.Attachments.Add(new Net.Mail.Attachment(strFile.Trim()));

    }

    }

    if (!strSMTPServer.Equals(string.Empty)) {

    System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient();

    {

    if (!strSMTPServerUser.Equals(string.Empty)) {

    smtp.UseDefaultCredentials = false;

    smtp.Credentials = new System.Net.NetworkCredential(strSMTPServerUser, strSMTPServerPwd);

    }

    smtp.Host = strSMTPServer;

    smtp.Port = strSMTPServerPort;

    smtp.Send(MailMsg);

    }

    } else {

    System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient();

    {

    smtp.Host = "localhost";

    smtp.Port = 25;

    smtp.Send(MailMsg);

    }

    }

    }

    }

    }

  • Jonathan manley

    SSC Enthusiast

    Points: 108

    mbova407 (11/4/2010)


    Good Article. Two suggestions

    I would make the attribute appear in the same code window as the method. At first it looks confusing because you have

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    That ends in a continuation character that goes nowhere.

    Then maybe a little more explanation as to why this attribute is needed.

    Represents an abstraction of the caller's context, which provides access to the SqlPipe, SqlTriggerContext, and WindowsIdentity objects.

    or

    The SqlContext object can be used to obtain the trigger context (for code executing in a trigger) and the pipe object (when available, such as in stored procedures).

    Definitions are taken from the MSDN.

    The Actual VB code should look like this:

    Public Class SQLCLREmail

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub SendEmail(ByVal recipients As String _

    , ByVal CC as String _

    , ByVal BCC as String _

    , ByVal subject As String _

    , ByVal from As String _

    , ByVal body As String _

    , ByVal strAttachments as String _

    , ByVal strSMTPServer as String _

    , ByVal strSMTPServerPort as String _

    , ByVal strSMTPServerUser as String _

    , ByVal strSMTPServerPwd as String)

    With the start of the Sub going directly under the Attribute hence the line continuation.

  • John Pluchino

    SSC Enthusiast

    Points: 179

    The source code displayed in a very small window which did not scroll right far enough to see all of the code on certain lines that were long. Is there a way to expand the viewing window? Is there a way to copy the source code samples? I am new at this so my question may seem trivial but it is a show stopper for me. Thanks for any hints you might provide...John

  • Charles Kincaid

    SSChampion

    Points: 13593

    Very good article. I had fogotten that the CLR was available in Express. There are all kinds of things that you can do in the CLR that can't (or should not) be done in regulare SQL. I say that as you can do much more extensive error trapping and security checking in an easier manner in C# or VB than the SQL syntax. Grunting through strings is procedural and this can give you better speed.

    ATBCharles Kincaid

  • Jonathan manley

    SSC Enthusiast

    Points: 108

    JPluchino (11/4/2010)


    The source code displayed in a very small window which did not scroll right far enough to see all of the code on certain lines that were long. Is there a way to expand the viewing window? Is there a way to copy the source code samples? I am new at this so my question may seem trivial but it is a show stopper for me. Thanks for any hints you might provide...John

    Using your mouse start at the upper left side and you should get a text selector mouse icon, left click and hold, then drag you mouse to the bottom.

Viewing 15 posts - 1 through 15 (of 55 total)

You must be logged in to reply to this topic. Login to reply