SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 sp_SMTPMail stored procedure problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 11/24/2003 :  08:37:39  Show Profile  Visit elody's Homepage  Reply with Quote
I am using the sp_SMTPMail stored procedure with the following code

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) = 'theserver.com'


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, 'Melody', @SenderName
EXEC @resultcode = sp_OASetProperty @oMail, 'elody@hotmail.com', @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

exec sp_SMTPMail @SenderName='Ebony', @SenderAddress='elody@hotmail.com',
@RecipientName = 'Melody',
@RecipientAddress = 'melody@aol.com',
@Subject='SQL Test', @body='Hello, this is a test email from SQL Server'

While the stored procedure has been created, when I try and execute it I get the follwoing error message

Server: Msg 217, Level 16, State 1, Procedure sp_SMTPMail, Line 18
Maximum stored procedure nesting level exceeded (limit 32).

(the email address and server name have been made up for security purposes)

Can some one please help me I need to have this working like yesterday.


Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 11/24/2003 :  08:40:09  Show Profile  Visit Merkin's Homepage  Reply with Quote
Is that the exact code you are running ?

Put a GO between the end of the proc, and the call to it you have put in as a test. It looks to me like it's getting into a loop.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000