Running stored procedure from SQL Server Agent Job

[brobinson117]

I was wondering if anybody knew how to run a stored procedure from within a SQL scheduled job.

What I trying to do is send out an email reminder based on a date difference from a date field and current date.

What I tried below was just a stored procedure that sends out an email, which I entered under steps on the properties of the job:

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) = 'program3'

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, 'FromName', 'Admin'
EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', 'brobinson@ylm.ca'

EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, 'Bruce', 'brobinson117@yahoo.ca'

EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', 'Test Email from SQL'
EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', 'Test'


EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL

EXEC sp_OADestroy @oMail
END


SET nocount off
GO

DROP PROCEDURE sp_SMTPMail;
GO

Then under schedule I then schedule it to run once at certain time.

The job runs successfully but I can't tell if it sent an email or if it just created and dropped the stored procedure.

[LeeU]

Be sure to set your code within code tags (http://www.webdeveloper.com/forum/misc.php?do=bbcode) for easier reading. I changed it above for you.

[mattyblah]

create the procedure in a database, then call the procedure from the job. Or, don't even bother creating the stored procedure and just execute the sql as it is. What you're doing right now is creating the procedure and dropping it, not executing it.