Post reply

Sending E-Mail from SQL 2008 Express

  • November 4, 2010 at 11:30 am

    #1246715

    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 0

    A .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)

    .

  • November 4, 2010 at 11:34 am

    #1246722

    karen.scott260 (11/4/2010)

    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 0

    A .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)

    .

    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.

  • November 4, 2010 at 7:46 pm

    #1246965

    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?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 4, 2010 at 8:24 pm

    #1246978

    This 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 0

    A .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:25

    System.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.

    .

  • November 4, 2010 at 11:58 pm

    #1246988

    @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).

  • November 5, 2010 at 7:40 am

    #1247127

    amet gbas (11/4/2010)

    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)

    .

    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.

    Kind regards,
    Gift Peddie

  • November 5, 2010 at 1:35 pm

    #1247305

    I am receiving the following message:

    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:

    Does this relate to Gift Peddie's response?

  • November 5, 2010 at 2:04 pm

    #1247315

    mytrashcan (11/5/2010)

    I am receiving the following message:

    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:

    Does this relate to Gift Peddie's response?

    No that relates to SQL CLR and related filesystem permissions for the .NET assembly.

    Kind regards,
    Gift Peddie

  • November 5, 2010 at 8:20 pm

    #1247387

    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

  • November 8, 2010 at 7:04 am

    #1247645

    mytrashcan (11/5/2010)

    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

    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.

    Kind regards,
    Gift Peddie

  • November 8, 2010 at 11:25 am

    #1247801

    It works well with Express 2005 but I do have the latest .net framework on that server.

  • November 8, 2010 at 11:26 am

    #1247803

    It works well with Express 2005 but I do have the latest .net framework on that server.

  • November 18, 2010 at 9:15 am

    #1252601

    hello

    Good 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 ?

    Clement

  • November 18, 2010 at 9:19 am

    #1252604

    I would validate the physical path and permissions.

  • November 18, 2010 at 9:24 am

    #1252606

    APrisk2 (11/18/2010)

    I would validate the physical path and permissions.

    Another thing this is the attachment definition not enough to hold large files so change that to NVarchar (max).

    @strAttachments [nvarchar](4000)

    Kind regards,
    Gift Peddie

Viewing 15 posts - 16 through 30 (of 54 total)

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