Sending email in a stored procedure with MSSQL 2000 under Windows Server 2003 (up-to-date SPs)

Our server runs Windows Server 2003 Standard Edition with SQL Server 2000 with the most recent service packs. We have IMAIL installed but not Exchange Server nor Outlook.

I am inexperienced in the following issue (re: email and stored procs) so please be as clear as possible in answering this: what is the easiest way that we can send an email from a stored procedure? Thank you very much. A simple example would be much appreciated
Who is Participating?
William ElliottConnect With a Mentor Sr Tech GuruCommented:
crap, sorry, yea that does require and external COM component from here

i'm a little slow sometimes.  this should answer all your questions..
FYI it does require IIS to be installed on the sql server.

or here is you want to use sql mail
William ElliottSr Tech GuruCommented:

IF EXISTS (      SELECT NAME FROM sysobjects
                  WHERE name = 'sp_SMTPMail' AND type = 'P')
            DROP PROCEDURE sp_SMTPMail
--== Name              -        sp_SMTPMail
--== Purpose       -        Send an email using an SMTP Mailer COM Component
--== Input Parameters       -         @SenderName, @SenderAddress, @RecipientName, @RecipientAddress
--==                                        @Subject, @Body, @MailServer
--== Usage      -      exec sp_SMTPMail @SenderName='Damian', @SenderAddress='',
--==                  @RecipientName = 'Someone', @RecipientAddress = '',
--==                  @Subject='SQL Test', @body='Hello, this is a test email from SQL Server'
--== Created Date      -        Thursday, July 19, 2001
--== Created by            -        Damian Maclennan

Create Procedure sp_SMTPMail

      @SenderName varchar(100),
      @SenderAddress varchar(100),
      @RecipientName varchar(100),
      @RecipientAddress varchar(100),
      @Subject varchar(200),
      @Body varchar(8000),
      @MailServer varchar(100) = 'localhost'
      SET nocount on
      declare @oMail int --Object reference
      declare @resultcode int
      EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT
      if @resultcode = 0
            EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver
            EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
            EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress
            EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress
            EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
            EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body
            EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL
            EXEC sp_OADestroy @oMail
      SET nocount off
rorickaAuthor Commented:

Thank you thank you. But...

I created the sp and executed it. It didn't send any email, so I added a print statement and I see I'm getting error -2147221005 (Invalid class string) from the line

sp_OACreate 'SMTPsvg.Mailer', @oMail OUT

Do I need smtpsvg.dll here? (Honestly, I know just enough to be dangerous here!)

Thank you for the help so far. Can you get me over this hump?



How do I debug this thing? Is there a log I can look at? Does this thing use IMAIL? (W/o wishing to sound like I understand more than I do, what is the default email client here?)
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

William ElliottSr Tech GuruCommented:
create  a stored procedure called sp_SMTPMail

then open query analyzer and paste this

exec sp_SMTPMail
@RecipientName = 'Someone',
@RecipientAddress = '',
@Subject='SQL Test',
@body='Hello, this is a test email from SQL Server'
rorickaAuthor Commented:
Hi weellio,

Hmmm...well, that's just about exactly what I already did. But I did it again, exactly using your text (except for the addresses, of course). Same results. No email is being sent. And when I look inside the sp at what may be going wrong, I see an error code is being generated, as I described above.

Any ideas would be much appreciated. Remember, this is MSSQL Server 2000. Neither Outlook nor Exchange are running on the server.
rorickaAuthor Commented:
You know what, I found this:

and got it working (it was a bit tricky, requiring tweaks to the settings for the IMAIL running on the server and using the local IP for @server when calling their extended stored proc. -- Y'have to install a .dll as well.)

But I will look at what you sent and thank you for trying to help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.