November 3, 2010 at 9:54 pm
Comments posted to this topic are about the item Sending E-Mail from SQL 2008 Express
November 4, 2010 at 2:19 am
Nice one Andrew. I can hardy wait to test it.
Regards,
Iulan
November 4, 2010 at 2:54 am
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)
.
November 4, 2010 at 3:02 am
Nice Article.
We can create a linked server and access the msdb.dbo.sp_send_dbmail.
November 4, 2010 at 3:35 am
Good article... Nice
November 4, 2010 at 3:42 am
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.
November 4, 2010 at 4:14 am
You have just introduced me to CLR...Thanks for a great article!!
November 4, 2010 at 4:37 am
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.
November 4, 2010 at 7:32 am
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);
}
}
}
}
}
}
November 4, 2010 at 7:35 am
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.
November 4, 2010 at 7:36 am
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);
}
}
}
}
}
November 4, 2010 at 7:40 am
mbova407 (11/4/2010)
Good Article. Two suggestionsI 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.
November 4, 2010 at 9:40 am
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
November 4, 2010 at 10:56 am
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
November 4, 2010 at 11:04 am
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 54 total)
You must be logged in to reply to this topic. Login to reply