Sending an SMTP email from stored procedure

[vamoose]

I am using Windows 2003 Server Standard ans MSSql 2000 and attempting to send an email using this stored procedure 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) = 'localhost'

AS

SET nocount on
SET @SenderName = 'John Smith'
SET @SenderAddress = 'smtprelay.advance.com'
SET @RecipientName = 'John Smith'
SET @RecipientAddress = 'john.smith@advance.com'
SET @Subject = 'Testing SMTP email'
SET @Body = 'From SMTP server'

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

I am getting the error message:
Server: Msg 201, Level 16, State 4, Procedure sp_SMTPMail, Line 0
Procedure 'sp_SMTPMail' expects parameter '@SenderName', which was not supplied.

This is new to me and I don't quite understand what is going on or what is missing. Any assistance would be greatly appreciated. Thank you.

 

[philhege]

Is there a good reason you're not using SQL Mail? When does the error occur: when you try to create the SP or when you call it?

Post an example of the statement you use to call the SP.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[SQLSister]

YOu are defining variables as input variables and then setting them in the proc rather than when you execute the proc.

"NOTHING is more important in a database than integrity." ESquared

 

[vamoose]

The error occurs when I use "execute sp_SMTPMail" from query analyzer.

I am not aware of SQL Mail, please tell me more of this. I need to send an automated email, I planned on using a stored procedure to accomplish this so I can schedule it as job.

 

[SQLSister]

yes and you have input variables that are not optional so the execute call must include the values for them. like this example:
exec my_proc 'test', 'test2', 'test3'
or if you have optional variables and want to be able to skip some
exec my_proc @myvariable = 'test', @mythirdvariable = 'test3'

"NOTHING is more important in a database than integrity." ESquared

 

[vamoose]

How about something like this ?

CREATE Procedure sp_SMTPMail

@SenderName varchar(100) = 'John Smith',
@SenderAddress varchar(100) = 'smtprelay.advance.com',
@RecipientName varchar(100) = 'John Smith',
@RecipientAddress varchar(100) = 'john.smith@advance.com',
@Subject varchar(200) = 'Testing SMTP email',
@Body varchar(8000) = 'From SMTP server',
@MailServer varchar(100) = 'localhost'

AS

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

 

[SQLSister]

I would not do that or the test values will be put in anytime you don't send one which means they could end up on a real email. Simply send the values you want when you execute the proc.

"NOTHING is more important in a database than integrity." ESquared