I currently have a web form posting back to a SQL table using a Stored
Procedure. Part of this SP is that it pulls data from another table
and inserts a new row into the registration table.

I want to have a trigger on the registration table that will fire when
the row is inserted which will use the sp_send_cdosysmail sproc to send
an e-mail to the user.

However, I want to be able to include the value of one of the fields
within the BODY of the message. I can't find a way to include
parameters/variables within the Body of a message using
sp_send_cdosysmail and it's driving me nuts.

Here's what I have in a sproc (not a trigger) that executes
sp_send_cdosysmail...I currently pass a parameter for the "To" e-mail
address and that works fine.

_________________________________________________
EXEC sp_send_cdosysmail
'fromemailaddress (AT) testcompany (DOT) com',
@stremail, <--This is the Parameter passed for the "To" e-mail addy
-->

'Test Subject',
'Test Body,
Additional Text
Additional Text
<--THIS IS WHERE I WANT TO PUT THE PARAMETER-->
Additional Text
Additional Text'

__________________________________________________ _


Is there any way to do this?

The sp_send_cdosysmail I used is the standard MS one..Here it is for
reference:


Thanks for any help offered!

Elliot



CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000)

/************************************************** *******************

This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/de..._messaging.asp

************************************************** *********************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)

--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','1'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',
'SMTPServer'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I'm not sure I understand you completely, but here's one possibility:

declare @Body varchar(4000)
declare @IncludeThis varchar(100)

set @Body = 'This is the body of my email, and I want my value to go
<<HERE>>'
set @IncludeThis = 'at the end of this line.'
set @Body = replace(@Body, '<<HERE>>', @IncludeThis)

exec sp_send_cdosysmail ...

By the way, sending emails from triggers is usually considered a rather
risky approach - since triggers operate in a transaction, if the email
process takes a long time or hangs, then the trigger may block other
users.

An alternative solution is to add a flag column to your registrations
table which indicates if an email has been sent, then schedule a job to
run every half hour (or whatever) and send out the emails based on the
flag.

Simon

Thanks for this. While that wouldn't work as you described (through no
fault of yours...I realize my question was a little convoluted) you
pointed me in the right direction.

I just needed to declare and set the @Body in the SProc that called the
sp_send_csosysmail procedure and then pass it on as the body of the
message. Then it's just standard T-SQL to combine a string with the
variable. It seems so simple now.

Also, thanks for the pointer about triggers as I hadn't thought of
that. I do have a trigger, but it executes a SP on the same database
that does all the actual work.

Thanks for the help!

Elliot

Simon Hayes wrote: