Sending JMail email with SQL stored procedure

I really must share more interesting stuff as a I come across it.

I've been having fun recently using sp_OACreate in SQL stored procedures to use COM objects. For example, I came across this link that shows how to send email using the JMail COM object in a stored procedure.

A couple of helpful hints:

  1. You need to  'OLE Automation Procedures are enabled' as true, in SQL Surface Area Configuration
  2. You'll need a user with rights on the Master db. I created a separate user just for calling these stored procedures, and gave the them the following rights:

USE [master]
GRANT EXECUTE ON dbo.sp_OACreate TO [user]
GO
GRANT EXECUTE ON dbo.sp_OASetProperty TO [user]
GO
GRANT EXECUTE ON dbo.sp_OAMethod TO [user]
GO
GRANT EXECUTE ON dbo.sp_OAGetErrorInfo TO [user]
GO

My only problem so far is JMail won't send an attachment. I've tried messing with permissions on the file to be attached but that didn't help.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s