October 19, 2012 at 5:57 am
-- I am hoping not to use cursor
-- Query is
select * from Orders where EmailSent = 0
--I want to loop through records with above query send email and Set Emailsent field
--Email Code
EXEC msdb.dbo.sp_send_dbmail
@profile_Name ='Administrator',
@recipients= Orders.CustomerEmailAddress ,
@subject = 'Please send Feedback for Order#' + Orders.OrderNum ,
@body = 'Please send Feedback for Order#' + Orders.OrderNum
--Update Field
set Orders.EmailSent = 1
October 19, 2012 at 6:20 am
here's an example of what you are asking;
you'll have to use a cursor if you want to send emails on a per-email basis from the data.
remember that when you assign parameters, you cannot concatent/use functions/manipulate anything in the assignment, that has to occur BEFORE you assign it.
--so this is bad:
EXEC msdb.dbo.sp_send_dbmail
@subject = 'Please send Feedback for Order#' + Orders.OrderNum
--this is the correct way:
declare @var varchar(128)
SET @var ='Please send Feedback for Order#' + Orders.OrderNum
EXEC msdb.dbo.sp_send_dbmail
@subject =@var
and here's the full exmaple:
DECLARE
@isql VARCHAR(2000),
@email VARCHAR(64),
@order VARCHAR(64),
@emailSubject varchar(128),
@emailBody varchar(128)
DECLARE c1 CURSOR FOR
SELECT
CustomerEmailAddress,
OrderNum
FROM Orders
WHERE EmailSent = 0
OPEN c1
FETCH NEXT FROM c1 INTO @email,@order
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT
@emailSubject = 'Please send Feedback for Order#' + @order,
@emailBody = @emailSubject --you'd build a bigger, more descriptive string for the body.
--you cannot concatenate strings as the parameters for a query, they must be eitehr a static string or an already built variable.
EXEC msdb.dbo.sp_send_dbmail
@profile_Name ='Administrator',
@recipients= @email ,
@subject = @emailSubject,
@body = @emailBody
FETCH NEXT FROM c1 INTO @email,@order
END
CLOSE c1
DEALLOCATE c1
Lowell
October 19, 2012 at 6:52 am
Thanks for the reply.
Another Quick Question. My Email body is very long.
Ideally i would like to store the Body in a text file and read the text file into a SQL Variable like
set @EmailBody = 'read from the text file
Is it possible ?
October 19, 2012 at 6:52 am
Thanks for the reply.
Another Quick Question. My Email body is very long.
Ideally i would like to store the Body in a text file and read the text file into a SQL Variable like
set @EmailBody = 'read from the text file
Is it possible ?
October 19, 2012 at 6:53 am
Thanks for the reply.
Another Quick Question. My Email body is very long.
Ideally i would like to store the Body in a text file and read the text file into a SQL Variable like
set @EmailBody = 'read from the text file
Is it possible ?
October 19, 2012 at 6:54 am
Thanks for the reply.
Another Quick Question. My Email body is very long.
Ideally i would like to store the Body in a text file and read the text file into a SQL Variable like
set @EmailBody = 'read from the text file
Is it possible ?
October 19, 2012 at 6:58 am
Thanks for the reply.
Another Quick Question. My Email body is very long.
Ideally i would like to store the Body in a text file and read the text file into a SQL Variable like
set @EmailBody = 'read from the text file
Is it possible ?
October 19, 2012 at 7:05 am
skb 44459 (10/19/2012)
Thanks for the reply.Another Quick Question. My Email body is very long.
Ideally i would like to store the Body in a text file and read the text file into a SQL Variable like
set @EmailBody = 'read from the text file
Is it possible ?
of course;
it's just a separate select statement:
SELECT @EmailBody = EmailTemplate
FROM Campaigns WHERE templateID = 42
--assuming there are customized fields for find and replace?
SET @EmailBody = REPLACE(@EmailBody, '[firstnamePlaceholder', @FirstNameFromCursor;
Lowell
October 19, 2012 at 7:24 am
Actually i want to read from a text file . \\server1\temp\EmailBody.txt into a variable
October 27, 2012 at 11:07 pm
skb 44459 (10/19/2012)
Actually i want to read from a text file . \\server1\temp\EmailBody.txt into a variable
Did you ever get an answer for this?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply