sp_SMTPMail stored procedure problem

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

All Forums

Site Related Forums

Article Discussion

sp_SMTPMail stored procedure problem

Author

Topic

elody
Starting Member

1 Post

Posted - 2003-11-24 : 08:37:39

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'

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, '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
END

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.

Thanks

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-24 : 08:40:09

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.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources