Using OLE Servers in SQL Server Stored Procedures

---

Discussion

SQLServer stored procedures can interface to OLE Servers in a similar manner to Visual Basic. As an example, two sets of code are shown below. Each one creates and sends an E-mail message through the IDSMail OLE Server:

For VIM/MAPI/VINES/MHS Mail Systems

create procedure sp_quote_mail_send
as
begin

declare @hr int
declare @object int
declare @recipient char(35)

exec @hr=sp_OACreate 'IDSMailInterface.Server', @object OUTPUT
exec @hr=sp_OASetProperty @object,'ObjectKey','ABC123'
exec @hr=sp_OAMethod @object,'NewMessage'
exec @hr=sp_OASetProperty @object,'Subject','Sql Server E-mail Test'
exec @hr=sp_OASetProperty @object,'Message','Here id the test message.'
select @recipient='Jim Smith'
exec @hr=sp_OAMethod @object,'AddRecipientTo',NULL, @recipient
exec @hr=sp_OAMethod @object,'Send'

end

For SMTP Internet Mail

create procedure sp_quote_mail_send
as
begin

declare @hr int
declare @object int
declare @recipient char(35)

exec @hr=sp_OACreate 'IDSMailInterface.Server', @object OUTPUT
exec @hr=sp_OASetProperty @object,'ObjectKey','ABC123'
exec @hr=sp_OASetProperty @object,'SMTPServer','mySMTPServer.com'
exec @hr=sp_OASetProperty @object,'MailSystem',10
exec @hr=sp_OAMethod @object,'NewMessage'
exec @hr=sp_OASetProperty @object,'From','me@mycompany.com'
exec @hr=sp_OASetProperty @object,'Subject','Sql Server E-mail Test'
exec @hr=sp_OASetProperty @object,'Message','Here id the test message.'
select @recipient='jsmith@acme.com'
exec @hr=sp_OAMethod @object,'AddRecipientTo',NULL, @recipient
exec @hr=sp_OAMethod @object,'Send'

end