Please start any new threads on our new site at http://forums.sqlteam.com. 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
 
Register Now and get your question answered!
Username:
Password:
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  

elody
Starting Member

USA
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'

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!

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


Damian
Go to Top of Page
  Previous Topic Topic Next 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.14 seconds. Powered By: Snitz Forums 2000