November 6, 2007 at 4:36 pm
I am trying to set something up that can be run nightly as a job that will send emails out to users reminding them that they have an appointment with someone at a specific time and date. I have created the following stored procedure. (I've simplified where the data is coming from for the example.)
CREATE proc udsp_send_reminder_mail as
set nocount on
if exists(select top 1 1 from emailtest where apt_date = 11/6/2007)
begin
declare @participant varchar(50)
declare @email varchar(255)
declare @res int, @oMail int
declare mail_cursor cursor for select participant, email from emailtest where apt_date = 11/6/2007
exec @res = sp_OACreate 'CDO.Message', @oMail out
exec @res = sp_OASetProperty @oMail, 'Subject', 'Appointment Reminder'
--exec @res = sp_OASetProperty @oMail, 'From', 'info@server.com'
open mail_cursor
fetch next from mail_cursor into @participant, @email
while @@fetch_status = 0
begin
exec @res = sp_OASetProperty @oMail, 'To', @email
exec @res = sp_OASetProperty @oMail, 'TextBody', 'This is a reminder to let you know you have an appointment scheduled with ' + cast(@participant as varchar) + ' .'
exec @res = sp_OAMethod @oMail, 'Send', null
fetch next from mail_cursor into @participant, @email
end
close mail_cursor
deallocate mail_cursor
exec sp_OADestroy @oMail
return (0)
end
return(0)
go
I have two issues. When I attempt to create the procedure I get an error stating Error:Incorrect syntax near '+'.Number:102Severity:15State:1
Occurred on Line:17. When I remove + cast(@participant as varchar) + ' .' then the procedure is created with no errors. However, I will need to be able to do this to pass in some variable information into the body text of the email.
The other issue I'm having is that when I run the procedure it says it executes the statement but no emails are sent. When I change the code a little to see what I am getting on each line for @res I get a valid return value (0) for all the lines except the send line. I am assuming it is because I need to connect to a email server. Does anyone know what the method name or property name is so that I can set it to a server?
November 6, 2007 at 7:58 pm
as i remember it, parameters may not be constructed inline; they have to be built and then passed:
exec @res = sp_OASetProperty @oMail, 'TextBody', 'This is a reminder to let you know you have an appointment scheduled with ' + cast(@participant as varchar) + ' .'<--Not Allowed
declare @msg varchar(1000)
SET @msg = 'This is a reminder to let you know you have an appointment scheduled with ' + cast(@participant as varchar) + ' .'
exec @res = sp_OASetProperty @oMail, 'TextBody',@msg <--Allowed
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply