InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 16:39:02 GMT20http://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxBrilliantly simple stuff, many thanks!This solved an age-old problem for us, and we are highly indebted to people like you that take the time to share your knowledge.Once again, thanks! :-DWed, 18 Jul 2012 05:30:39 GMTJ-LKhttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx[quote]Therefore, users who are authenticated with SQL Server Authentication cannot attach files using @file_attachments. Windows does not allow SQL Server to provide credentials from a remote computer to another remote computer[/quote]I don’t think it is related because @file_attachment is local variable in SQL Server not related to system.net used for Microsoft SMTP mail. Please check the link below for the system stored procedure that defines attachment file size and related issues, the reason you can use Default for the connection properties but you still need to talk to system admin and Exchange admin. [url]http://msdn.microsoft.com/en-us/library/ms191442.aspx[/url]Mon, 20 Feb 2012 13:53:30 GMTGift Peddiehttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxActually, I wrote an enhanced version that allows for dynamic SMTP servers as well as logging to a SQL table. I'll update the article or send in a new one; should be able to have it out within the next week or so.Mon, 20 Feb 2012 13:35:59 GMTAPrisk2http://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxDo you think would be an answer for the problem (in sql 2008) where, using database mail to send email and attachments when logged on to sql with sql authentication, won't allow sending the attachment unless one has sysadmin permission?Here is the books online discussion:Database Mail uses the Microsoft Windows security context of the current user to control access to files. Therefore, users who are authenticated with SQL Server Authentication cannot attach files using @file_attachments. Windows does not allow SQL Server to provide credentials from a remote computer to another remote computer. Therefore, Database Mail may not be able to attach files from a network share in cases where the command is run from a computer other than the computer that SQL Server runs on.Have been googling this for days. No one seems to have solution for this problem other than to use windows authentication or give user greater permission, neither of which, is desirable. thanks for any thoughts on this.Mon, 20 Feb 2012 12:17:37 GMTjonnylewishttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxAny tips on how to make this work with an Open SMTP Server?Wed, 09 Feb 2011 22:28:33 GMTAditya Ahttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxI've tried the mail send as described here. Its working great except for the part where line breaks/carriage returns are removed from the body of the message. The way I've tried it so far is the p_SendEmail procedure is called via my procedure where the body of the message is concatenated(formatted with line breaks). The mail and attachments are recieved but the line breaks generated by my procedure gets removed.This is how I have generated the linebreaksSET @MSG ='Dear All,'SET @MSG=@MSG+CHAR(13)+'Executive : ' +@EXECUTIVE SET @MSG=@MSG+CHAR(13) SET @MSG=@MSG+CHAR(13)+'Start Date : ' +CONVERT(VARCHAR(20),@START_DATE,106) SET @MSG=@MSG+CHAR(13) SET @MSG=@MSG+CHAR(13)+'End Date : ' +CONVERT(VARCHAR(20),@END_DATE,106)Finally I pass @MSG as the body of the mail. When the mail is received all the line breaks/carriage returns are absent. Any idea on what's going wrong? The line breaks work properly when I use SQL Db mail.--Replaced CHAR(13) with < /br>. Works fineMon, 20 Dec 2010 02:08:38 GMTAditya Ahttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxwe find issue to our problem. we add content-disposition option on the attachment file property.See the code below : If Not strAttachments.Equals(String.Empty) Then Dim strFile As String Dim strAttach() As String = strAttachments.Split(";") For Each strFile In strAttach Dim Data As Net.Mail.Attachment = New Net.Mail.Attachment(strfile.trim()) Dim Disposition As Net.Mime.ContentDisposition = Data.ContentDisposition Disposition.CreationDate = System.IO.File.GetCreationTime(strfile.trim()) Disposition.ModificationDate = System.IO.File.GetLastWriteTime(strfile.trim()) Disposition.ReadDate = System.IO.File.GetLastAccessTime(strfile.trim()) Mailmsg.Attachments.Add(Data) Next End IfIt works well; so thanks a lot to everyone for help.Very good article and very useful for us.king regardsTue, 23 Nov 2010 10:51:12 GMTclement.degrootehttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx[quote][b]clement.degroote (11/22/2010)[/b][hr]Hello.Sorry, but I don't understand. Could you tell me more ?[/quote]Sure so instead of[code="vb"]Imports System.Net Imports System.Net.Mail[/code]use the code below instead[code="vb"]Imports System.Net Imports System.Net.MailImports System.IO[/code]The later code lets you access the file system or the streams like memory stream which is one of the child classes of the Base Class TextWriter.Mon, 22 Nov 2010 08:02:38 GMTGift Peddiehttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxHello.Sorry, but I don't understand. Could you tell me more ?Mon, 22 Nov 2010 07:45:39 GMTclement.degrootehttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx[quote][b]clement.degroote (11/19/2010)[/b][hr]yes i did;control total to everyone[/quote]If permissions is not the issue it may related to the attachement definition in the code. There are two ways to send attachments with System.net.mail one is to use filesystem or to use the streams both require System.IO Import or Using statement.Fri, 19 Nov 2010 08:21:20 GMTGift Peddiehttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx[quote][b]dmbaker (11/19/2010)[/b][hr][quote][b]Jeff Moden (11/18/2010)[/b][hr][quote][b]Jeff Moden (11/4/2010)[/b][hr]I have to ask because I just don't know... Hopefully, one of you do. Is CDONTS and sp_OA* available in SQL Server Express?[/quote]Anyone?[/quote]I'm not certain, but I don't think CDONTS will be available. Don't think it'd be part of the install of SQL Server Express, but it *might* be part of the OS install, or might come with Microsoft Office if you've got the right parts installed. You're more likely to have CDOSYS I think, if it's newer version of Windows. In googling a bit it looks like maybe SQL Server Express does have email stuff on the SQL Server side, but the core email components (DLLs) are not included and emailing-related services are disabled and buried.Sorry for the waffly answer. :-)[/quote]I am running 2008 developer, 2005 standard and Express and yes Express comes with the sp_OA just like SQL Server 2008 developer but no CDONTS. The reason those are COM dll which the developer division have depreciated since 2005 version of .NET.Fri, 19 Nov 2010 07:33:59 GMTGift Peddiehttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx[quote][b]Jeff Moden (11/18/2010)[/b][hr][quote][b]Jeff Moden (11/4/2010)[/b][hr]I have to ask because I just don't know... Hopefully, one of you do. Is CDONTS and sp_OA* available in SQL Server Express?[/quote]Anyone?[/quote]I'm not certain, but I don't think CDONTS will be available. Don't think it'd be part of the install of SQL Server Express, but it *might* be part of the OS install, or might come with Microsoft Office if you've got the right parts installed. You're more likely to have CDOSYS I think, if it's newer version of Windows. In googling a bit it looks like maybe SQL Server Express does have email stuff on the SQL Server side, but the core email components (DLLs) are not included and emailing-related services are disabled and buried.Sorry for the waffly answer. :-)Fri, 19 Nov 2010 07:09:29 GMTdmbakerhttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxyes i did;control total to everyoneFri, 19 Nov 2010 05:41:47 GMTclement.degrootehttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxAnd you verified the NTFS permissions?Fri, 19 Nov 2010 04:47:43 GMTAPrisk2http://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxYes, UNC path. I tried on the server and on my computer. And I tried server/computer name and with IP adress.Same problem : mail is sending but without attach fileAnd there isn't any error message and I've no idea how debug this process...Fri, 19 Nov 2010 04:13:34 GMTclement.degrootehttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxIt was changed to a shared path - do you mean a UNC path? Is the path to the file something like this: \\server\folder? If so, then try mapping that to a local drive and try it again.Fri, 19 Nov 2010 03:32:15 GMTAPrisk2http://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxThank you for answer.I change path and permissions with a share folder with permission to everyone.But no change : mail is sending, we receive it well but without attach file.Other idea ?Fri, 19 Nov 2010 01:02:51 GMTclement.degrootehttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx[quote][b]Jeff Moden (11/4/2010)[/b][hr]I have to ask because I just don't know... Hopefully, one of you do. Is CDONTS and sp_OA* available in SQL Server Express?[/quote]Anyone?Thu, 18 Nov 2010 19:48:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx[quote][b]APrisk2 (11/18/2010)[/b][hr]I would validate the physical path and permissions.[/quote]Another thing this is the attachment definition not enough to hold large files so change that to NVarchar (max).[code="other"] @strAttachments [nvarchar](4000)[/code]Thu, 18 Nov 2010 09:24:12 GMTGift Peddiehttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxI would validate the physical path and permissions.Thu, 18 Nov 2010 09:19:45 GMTAPrisk2http://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxhelloGood article. We did it and it works well.But we have a problem to send attach file.We try sending .pdf, .txt : the mail is sending but not the attach file.Any idea ?ClementThu, 18 Nov 2010 09:15:55 GMTclement.degrootehttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxIt works well with Express 2005 but I do have the latest .net framework on that server.Mon, 08 Nov 2010 11:26:33 GMTjmoss111-506838http://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxIt works well with Express 2005 but I do have the latest .net framework on that server.Mon, 08 Nov 2010 11:25:43 GMTjmoss111-506838http://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx[quote][b]mytrashcan (11/5/2010)[/b][hr]Thanks, but i dont understand what you are telling me.I am getting this exact message in two environments; first is Vista Home with SQL Express, second is XP with SQL 2008. Help!Thanks[/quote]Your error is related to permissions to use CLR code in SQL Server, which could be related to disabling UAC in Vista and making sure the account used to run the code can import assembly into SQL Server.Let explain if the error is related to SMTP the code in XP will run because IIS5.5 which comes with all XP except XP home comes with SMTP. While Vista even Vista Ultimate and pro comes without SMPT so you will need to download free SMTP to run that code. Another thing, Vista home may to run that code because IIS 7 in Vista home comes with limited features. What I mean is SMTP code uses authentication, Windows authentication is not included with IIS 7 in Vista home.Mon, 08 Nov 2010 07:04:30 GMTGift Peddiehttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxThanks, but i dont understand what you are telling me.I am getting this exact message in two environments; first is Vista Home with SQL Express, second is XP with SQL 2008. Help!ThanksFri, 05 Nov 2010 20:20:23 GMTmytrashcanhttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx[quote][b]mytrashcan (11/5/2010)[/b][hr]I am receiving the following message:Msg 6522, Level 16, State 1, Procedure p_SendEMail, Line 0A .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: Does this relate to Gift Peddie's response?[/quote]No that relates to SQL CLR and related filesystem permissions for the .NET assembly.Fri, 05 Nov 2010 14:04:15 GMTGift Peddiehttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxI am receiving the following message:Msg 6522, Level 16, State 1, Procedure p_SendEMail, Line 0A .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: Does this relate to Gift Peddie's response?Fri, 05 Nov 2010 13:35:34 GMTmytrashcanhttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx[quote][b]amet gbas (11/4/2010)[/b][hr]hi..tested it an get this error while trying to run stored procedure;Msg 6522, Level 16, State 1, Procedure p_SendEMail, Line 0A .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).[/quote]If you tested the code in your box you need IIS installed with the SMTP component running, using the code in production depends on how the SMTP server used to send the message is configured. In both Windows form and Web application because the required config information is in the code changes are needed for the code to run if your SMTP is a relay server or comes with rectrictions because Exchange admins don't like these code running.Fri, 05 Nov 2010 07:40:56 GMTGift Peddiehttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx@mytrashcan the reason for error is related port number/ipaddress of smtp server/firewall setting.The port number used by gmail is 587 rather than 25 and to find ipaddress ping "smtp.gmail.com" (you can even use smtp.gmail.com).Thu, 04 Nov 2010 23:58:25 GMTblessonblessonshttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxThis has started me on my way to looking into CLR linked procs as well; however, I cant get this to work.I am using smtp.gmail.com as my smtp server, also with my email ID and password as my smtp User and PWD and keeping the port at 25. I get the following error:Msg 6522, Level 16, State 1, Procedure p_SendEMail, Line 0A .NET Framework error occurred during execution of user-defined routine or aggregate "p_SendEMail": System.Net.Mail.SmtpException: Failure sending mail. ---> System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 74.125.157.109:25System.Net.Sockets.SocketException: at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress) at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)System.Net.WebException: at System.Net.ServicePoint.GetConnection(PooledStream PooledStream, Object owner, Boolean async, IPAddress& address, Socket& abortSocket, Socket& abortSocket6, Int32 timeout) at System.Net.PooledStream.Activate(Object owningObject, Boolean async, Int32 timeout, GeneralAsyncDelegate asyncCallback) at System.Net.PooledStream.Activate(Object owningObject, GeneralAsyncDelegate asyncCallback) at System.Net.ConnectionPool.GetConnection(Object owningObject, GeneralAsyncDelegate asyncCallback, Int32 creationTimeout) at System.Net.Mail.SmtpConnection.GetConnection(String host, Int32 port) at System.Net.Mail.SmtpClient.Send(MailMessage message)System.Net.Mail.SmtpException: at System.Net.Mail.SmtpClient.Send(MailMessage message) 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)Any and all help is greatly appreciated..Thu, 04 Nov 2010 20:24:34 GMTmytrashcanhttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxI have to ask because I just don't know... Hopefully, one of you do. Is CDONTS and sp_OA* available in SQL Server Express?Thu, 04 Nov 2010 19:46:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx[quote][b]karen.scott260 (11/4/2010)[/b][hr]When I try to execute the sp in SQL this is the error that I get. Does anyone know what the problem could be?Msg 6522, Level 16, State 1, Procedure p_SendEMail, Line 0A .NET Framework error occurred during execution of user-defined routine or aggregate "p_SendEMail": System.NullReferenceException: Object reference not set to an instance of an object.System.NullReferenceException: 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).[/quote]Check the values that are being passed in with profiler or something to make sure they have a value and are not null.For example make sure that you are passing in a from, recipents, body, smtpserver, port user and password.Thu, 04 Nov 2010 11:34:23 GMTJonathan manleyhttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxWhen I try to execute the sp in SQL this is the error that I get. Does anyone know what the problem could be?Msg 6522, Level 16, State 1, Procedure p_SendEMail, Line 0A .NET Framework error occurred during execution of user-defined routine or aggregate "p_SendEMail": System.NullReferenceException: Object reference not set to an instance of an object.System.NullReferenceException: 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).Thu, 04 Nov 2010 11:30:06 GMTkaren.scott260http://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx[quote][b]JPluchino (11/4/2010)[/b][hr]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[/quote]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.Thu, 04 Nov 2010 11:04:51 GMTJonathan manleyhttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxVery 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.Thu, 04 Nov 2010 10:56:00 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxThe 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...JohnThu, 04 Nov 2010 09:40:56 GMTJPluchinohttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx[quote][b]mbova407 (11/4/2010)[/b][hr]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.[/quote]The Actual VB code should look like this:[code="other"]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) [/code]With the start of the Sub going directly under the Attribute hence the line continuation.Thu, 04 Nov 2010 07:40:38 GMTJonathan manleyhttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx[code="other"]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); } } } }}[/code]Thu, 04 Nov 2010 07:36:57 GMTAPrisk2http://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspxUse 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.Thu, 04 Nov 2010 07:35:23 GMTJonathan manleyhttp://www.sqlservercentral.com/Forums/Topic1015669-2829-1.aspx[quote][b]ashwin.nprabhu (11/4/2010)[/b][hr]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.[/quote]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.[code]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); } } } } }}[/code]Thu, 04 Nov 2010 07:32:34 GMTdmbaker