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:
- You need to 'OLE Automation Procedures are enabled' as true, in SQL Surface Area Configuration
- 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.
