Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 sp_SMTPMail stored procedure problem
 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) = ''


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, '', @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='',
@RecipientName = 'Melody',
@RecipientAddress = '',
@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  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000