Sending E-Mail from SQL 2008 Express

Question

Post reply

Sending E-Mail from SQL 2008 Express

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

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

karen.scott260 Newbie Points: 5 More actions · Answer 1

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)

.

Jonathan manley SSC Enthusiast Points: 108 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

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)

mytrashcan SSC Rookie Points: 45 More actions · Answer 4

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.

.

blessonblessons SSC Eights! Points: 959 More actions · Answer 5

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

Gift Peddie SSC Guru Points: 73570 More actions · Answer 6

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

mytrashcan SSC Rookie Points: 45 More actions · Answer 7

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?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 8

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

mytrashcan SSC Rookie Points: 45 More actions · Answer 9

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

Gift Peddie SSC Guru Points: 73570 More actions · Answer 10

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

jmoss111-506838 SSChasing Mays Points: 651 More actions · Answer 11

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

jmoss111-506838 SSChasing Mays Points: 651 More actions · Answer 12

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

clement.degroote SSC Journeyman Points: 88 More actions · Answer 13

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

APrisk2 SSC Journeyman Points: 87 More actions · Answer 14

I would validate the physical path and permissions.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 15

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