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.