SQLEXPRESS 2005 eMail With Attachment

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQLEXPRESS 2005 eMail With Attachment

Author

Topic

bobmcclellan
Starting Member

46 Posts

Posted - 2011-09-16 : 09:02:13

With sqlExpress 2005, I've used some code that I've found online
that allowed me to create a dll, then use CLR and an assembly to send mail from stored procedures using my gmail account.
This has worked great for a long time and still does....

I now have the need to include attachments.
I found a page online that looks like it will do exactly what I need but this page does not show how to create the dll.

Could you please help me understand how to do this part. I am going to include 2 links...

The 1st one shows how I built what I've been using for a year or so.
That's this one... http://www.mssqltips.com/sqlservertip/1795/send-email-from-sql-server-express-using-a-clr-stored-procedure/
I followed this one soup to nuts and it works like a charm but does not allow for including attachments.

This 2nd allows for including attachments but does not show how to build the dll.
This one I'd like to be able to work through and use.....http://www.asql.biz/files/amDBObj.htm

I know it goes without saying but.... I'd really appreciate any light you can shed on this for me.

Thanks in advance,
Bob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-16 : 13:06:25

why cant you use sp_send_Dbmail
http://technet.microsoft.com/en-us/library/ms190307.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bobmcclellan
Starting Member

46 Posts

Posted - 2011-09-16 : 13:55:41

Thanks for the reply,
Is sp_send_DBmail not part of SQL MAIL.
SQL MAIL is not supported in sqlExpress
http://msdn2.microsoft.com/en-us/library/ms165636(en-US,SQL.90).aspx

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-16 : 14:09:59

quote:
Originally posted by bobmcclellan

...
This 2nd allows for including attachments but does not show how to build the dll.

Hello Bob,

The required DLL is included in the "amDBObj" package and is found at the following link;

http://www.asql.biz/en/Download2005.aspx

Perhaps the following link will provide some insight as to how to register an assembly.

http://msdn.microsoft.com/en-us/library/tzat5yw6(v=vs.71).aspx

HTH.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-16 : 14:15:29

see this for one with attachments

http://www.sqlservercentral.com/articles/SQL+Server+Express/71341/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-16 : 14:18:54

quote:
Originally posted by visakh16

see this for one with attachments

http://www.sqlservercentral.com/articles/SQL+Server+Express/71341/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks for posting that great article.

bobmcclellan
Starting Member

46 Posts

Posted - 2011-09-16 : 14:34:08

Thanks to both of you.
I really appreciate it. I will check these out tonight and hopefully be on my way.
Thanks again!

bobmcclellan
Starting Member

46 Posts

Posted - 2011-09-17 : 00:06:39

spent hours on this tonight... I just can't seem to get it working.
I keep getting ..
Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed
I tried 1st using the other suggested links but had no luck getting them working.
I followed the example at http://www.asql.biz/files/amDBObj.htm.
ehorn was 100% correct. The dll was there. I followed the directions exactly.
the sp runs but returns a -1.
the message as stated above is: Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed

I'm using my gmail account and credentials with port 587. Same config works for the set up without the attachment ( using setup from the 1st link )

Any help will be very much appreciated.
thanks in advance...
bob

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-17 : 09:47:41

quote:
Originally posted by bobmcclellan

spent hours on this tonight... I just can't seem to get it working.
I keep getting ..
Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed
I tried 1st using the other suggested links but had no luck getting them working.
I followed the example at http://www.asql.biz/files/amDBObj.htm.
ehorn was 100% correct. The dll was there. I followed the directions exactly.
the sp runs but returns a -1.
the message as stated above is: Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed

I'm using my gmail account and credentials with port 587. Same config works for the set up without the attachment ( using setup from the 1st link )

Any help will be very much appreciated.
thanks in advance...
bob

Hello,

Certain components are not granted permission to the context of the .NET runtime. This component requires access to libraries which are likely not currently given permission.

Have a look at the 'runtime security policy' for .NET. It is likely that you need to add a permission set to the executor for the libraries it needs to call at runtime.

There are many articles pertaining to security policy configuration. You can read more here;

http://msdn.microsoft.com/en-us/library/xbk2s3fa(v=vs.71).aspx

HTH.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-17 : 10:17:22

quote:
Originally posted by ehorn

There are many articles pertaining to security policy configuration. You can read more here;

http://msdn.microsoft.com/en-us/library/xbk2s3fa(v=vs.71).aspx

HTH.

Here are a few more relating to the topic of Code Access Security and Code Access Permissions;

http://msdn.microsoft.com/en-us/library/930b76w0(v=vs.71).aspx
http://msdn.microsoft.com/en-us/library/h846e9b3.aspx

Some time back we wrote a multi-threaded FTP/Mail application for a client and it accessed libraries and resources which required special code access security policies (access to Sockets, File I/O, Event Logs, Reflection, etc...).

As one begins to deploy solutions which utilize more resources and operations within the environment, these security and privilege policies become even more important to understand for both the protection of the client and the hosting provider.

HTH.

bobmcclellan
Starting Member

46 Posts

Posted - 2011-09-19 : 08:49:13

Thanks ehorn.
I will check out the links tonight.
I really appreciate it. I hope to get through the
learning curve on this. It's frustrating trying
to get something working that seems like it should
not be that difficult.
Once again, thanks for the help.
..bob

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-19 : 09:10:20

yvw,

It does seem like a lot of effort when one is working through a solution.

When I consider dropping this type of functionality in a hosting provider. I would want controls in place to limit exposure to risk. Any competent hosting provider would demand it.

This process is automating the ability to programmatically pick up a file (any file) from a share and email it to someone outside the firewall). Security becomes something to not overlook. Any client would want to know that resource and risk exposure is limited and well-defined.

Best of luck.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources