Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Loop Through the records - send email - set the value of the field - Need Code Help Expand / Collapse
Author
Message
Posted Friday, October 19, 2012 5:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:39 AM
Points: 91, Visits: 214
-- 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
Post #1374769
Posted Friday, October 19, 2012 6:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 12,916, Visits: 32,080
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1374778
Posted Friday, October 19, 2012 6:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:39 AM
Points: 91, Visits: 214
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 ?
Post #1374793
Posted Friday, October 19, 2012 6:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:39 AM
Points: 91, Visits: 214
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 ?
Post #1374794
Posted Friday, October 19, 2012 6:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:39 AM
Points: 91, Visits: 214
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 ?
Post #1374795
Posted Friday, October 19, 2012 6:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:39 AM
Points: 91, Visits: 214
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 ?
Post #1374797
Posted Friday, October 19, 2012 6:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:39 AM
Points: 91, Visits: 214
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 ?
Post #1374800
Posted Friday, October 19, 2012 7:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 12,916, Visits: 32,080
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1374802
Posted Friday, October 19, 2012 7:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:39 AM
Points: 91, Visits: 214
Actually i want to read from a text file . \\server1\temp\EmailBody.txt into a variable
Post #1374815
Posted Saturday, October 27, 2012 11:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:34 PM
Points: 37,098, Visits: 31,648
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1377990
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse