[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

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
0
roricka
Asked:
roricka
  • 3
  • 3
1 Solution
 
weellioCommented:
http://www.sqlteam.com/article/sending-smtp-mail-using-a-stored-procedure

IF EXISTS (      SELECT NAME FROM sysobjects
                  WHERE name = 'sp_SMTPMail' AND type = 'P')
            DROP PROCEDURE sp_SMTPMail
GO
/*
--=============================================================================================
--== 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='damian@bdussy.com',
--==                  @RecipientName = 'Someone', @RecipientAddress = 'someone@someplace.com',
--==                  @Subject='SQL Test', @body='Hello, this is a test email from SQL Server'
--==
--== Created Date      -        Thursday, July 19, 2001
--== Created by            -        Damian Maclennan
--==                              merkin@sqlteam.com
--==                              www.bdussy.com/damian

--=============================================================================================
*/
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'
      AS      
      SET nocount on
      declare @oMail int --Object reference
      declare @resultcode int
      EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT
      if @resultcode = 0
      BEGIN
            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
      END      
      SET nocount off
GO
0
 
rorickaAuthor Commented:
weellio:

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?

roricka

 

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?)
0
 
weellioCommented:
create  a stored procedure called sp_SMTPMail

then open query analyzer and paste this

exec sp_SMTPMail
@SenderName='Roricka',
@SenderAddress='Roricka@myemailaddress.com',
@RecipientName = 'Someone',
@RecipientAddress = 'someone@someplace.com',
@Subject='SQL Test',
@body='Hello, this is a test email from SQL Server'
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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.
0
 
weellioCommented:
crap, sorry, yea that does require and external COM component from here http://www.serverobjects.com/products.htm

i'm a little slow sometimes.  this should answer all your questions..
FYI it does require IIS to be installed on the sql server.
http://support.microsoft.com/kb/312839

or here is you want to use sql mail
http://classicasp.aspfaq.com/email/how-do-i-send-e-mail-from-sql-server.html
0
 
rorickaAuthor Commented:
You know what, I found this:

http://www.sqldev.net/xp/xpsmtp.htm#Return_codes

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.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now