Sending Email - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Sending Email

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-02-09 : 11:54:12

Hi There,
Can someone help me please.
I Have 2 tables.
ActiveUsers & UserPosts
I need to send an email for each ActiveUser Telling them their UserPosts that have passed a certain date.

I have been able to send individual emails with something like:

DECLARE DemoCursor CURSOR FOR
SELECT
CAST(UserPosts.UserId As VarChar) As UserId,
UserPosts.Description,
CAST (CONVERT(varchar(10), UserPosts.CompletionDate, 103) AS VarChar)As 'CompDate',
ActiveUsers.FirstName,
ActiveUsers.LastName,
ActiveUsers.EmailAddress,
LTrim(datediff(day,getdate(),UserPosts.CompletionDate)) As 'Warn'
FROM
UserPosts Inner Join
ActiveUsers On UserPosts.UserId = ActiveUsers.UserId
WHERE datediff(day,getdate(),UserPosts.CompletionDate) = 5 AND ACompletionDate is Null OR ACompletionDate = 0

OPEN DemoCursor
FETCH NEXT FROM DemoCursor INTO @ID, @Desc, @PCompDate, @FName, @LName, @UserEmail, @Warn
Set @SubjectMessage = 'Action: ' + @ID + ' Description : ' + @Desc
Set @body = '<HTML>'
+ '<BODY>'
...

...
Which looks for something thats gone past a certain date and sends a warning email to @userEmail.

I need a step before that to loop through the UsersFile and for each user loop through File1 and for each record that = the user and is past a certain date add it to a row in the email. when there are no more records for that particular user, send the email. Then move on to the next user and do the same until we have no more users.
Can that be done?

Please ignore the code above as it has been edited and could be wrong.

Thanks for your help.

Best Regards,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-09 : 14:13:29

is UsersFile another table? or is it a physical file? if its a table, post its structure please

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-02-10 : 04:25:13

Hi visakh16,
Thanks for your reply.

This is the structure of the UsersFile:
Userld bigint PrimaryKey
Title varchar(50) Null
FirstName varchar(50) Null
LastName varchar(50)
EmailAddress varchar(100) Null

Thanks for your help.

Best Regards,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 09:31:08

for each user loop through File1

whats File1? is it value in UserPosts table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-02-10 : 10:02:37

Hi Visakh,

I'm sorry,
File 1 is actually the usersfile.
I would like to loop through this file and then get a list of records from the UserPost file where the userId's match and the expirydate has passed today's date.
I then need to send an email, showing each record found on a seperate line in the email.
Then do the whole process again with the next userId in the Usersfile.

So each user gets an email with a list of their userpost's that have expired.

Thanks again for taking the time to help me.

Best Regards,

Steve.

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 10:25:22

you need to have cursor based or row iteration based logic for that
seomthing like


SELECT required columns... INTO #Temp
FROM UserFile uf
INNER JOIN UserPosts up
ON up.UserId = uf.UserId
INNER JOIN ActiveUsers  au
On up.UserId = au.UserId
WHERE ExpiryDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

DECLARE @UserId int
SELECT @UserId =MIN(userID)
FROM #Temp

WHILE @UserId IS NOT NULL
BEGIN

SELECT required columns INTO #temp2
FROM #Temp
WHERE UserID=@UserID


EXEC sp_send_dbmail...

DROP TABLE #Temp2

SELECT @UserId =MIN(userID)
FROM #Temp
WHERE userID > @UserId


END

DROP TABLE #Temp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-02-10 : 10:39:42

Hi Visakh,

Thanks for that.

I'll give it a go.
Can't see the cursor code but will try to implement it.

Thanks for your help, I appreciate it.

Best Regards,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 10:42:02

welcome...let us know how you got
I've just given you the core idea make sure you add relevenat column details etc to it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-02-10 : 11:05:23

Hi Visakh,

I have run the query and it returns something like:

(113 row(s) affected)

(1 row(s) affected)

(10 row(s) affected)

(6 row(s) affected)

(49 row(s) affected)

(3 row(s) affected)

(7 row(s) affected)

How can I see the details of the rows affected to check they are right.
Also your code seems to indicate there are 3 files. There is only UserFile & UserPost, no file called ActiveUsers. I obviously caused this with my poor explanation.

Best Regards,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 11:08:58

quote:
Originally posted by Steve2106

Hi Visakh,

I have run the query and it returns something like:

(113 row(s) affected)

(1 row(s) affected)

(10 row(s) affected)

(6 row(s) affected)

(49 row(s) affected)

(3 row(s) affected)

(7 row(s) affected)

How can I see the details of the rows affected to check they are right.
Also your code seems to indicate there are 3 files. There is only UserFile & UserPost, no file called ActiveUsers. I obviously caused this with my poor explanation.

Best Regards,

Steve

include a select inside loop if you want to see results
ActiveUsers was specified in initial post so if its not there just ignore it in query


SELECT required columns... INTO #Temp
FROM UserFile uf
INNER JOIN UserPosts up
ON up.UserId = uf.UserId
INNER JOIN ActiveUsers  au
On up.UserId = au.UserId
WHERE ExpiryDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

DECLARE @UserId int
SELECT @UserId =MIN(userID)
FROM #Temp

WHILE @UserId IS NOT NULL
BEGIN

SELECT required columns INTO #temp2
FROM #Temp
WHERE UserID=@UserID


EXEC sp_send_dbmail...


SELECT * FROM #temp2

DROP TABLE #Temp2

SELECT @UserId =MIN(userID)
FROM #Temp
WHERE userID > @UserId


END

DROP TABLE #Temp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-02-10 : 11:38:58

Hi Visakh,

Yes, Use a select statement. Why didn't I think of that.
I honestly think I am going backwards in my learning.

I'm off home now so will test over the weekend or on Monday.

Have a great weekend.

Best Regards,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 11:52:58

no probs...
let me know once you're done it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-02-13 : 04:10:47

Hi Visakh,

Hope you had a good weekend.

I am managing to get the data I want but I have not got a clue of how to get the data into the body of the email.
In the past I have only ever had to send 1 row per email and used something like:
FETCH NEXT FROM DemoCursor INTO @ID, @Desc, @PCompDate, @FName, @LName, @UsersEmail, @Warn
Set @SubjectMessage = 'Doc: ' + @ID + ' Description : ' + @Desc
Set @body = '<HTML>'
+ '<BODY>'
+ 'You are logged as the owner of document : ' + @ID + ' ( ' + @Desc + ' ).'
+ 'This document is nearing the planned completion date of ' + @PCompDate + ''
+ '</BODY>'
+ '</HTML>'
But this time I need to add a number of rows from the select statement to each email.
As shown in my last post there is one with (113 row(s) affected)

Would you be able to help with the way I can add all rows from the select statement into the body of the email.

I know this takes your time and I do appreciate it.

Best Regards,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 09:55:17

why not append the values to a long string variable inside the cursor and then use it in email?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-02-13 : 11:00:07

Hi Visakh,

Ok I have nearly got this working as I want but there is 1 odd thing.
I keep getting the last record twice.

This is the text from 1 of the emails, please see the bottom 2 lines:
Id Description Planned Completion Date
84 Document 84 01/01/2012
133 Document 133 31/12/2011
527 Document 527 01/12/2011
528 Document 528 01/12/2011
531 Document 531 01/12/2011
537 Document 537 31/12/2011
544 Document 544 31/12/2011
557 Document 557 18/10/2011
671 Document 671 01/12/2011
679 Document 679 31/12/2011
680 Document 680 31/12/2011
681 Document 681 31/12/2011
683 Document 683 31/12/2011
707 Document 707 31/12/2011
724 Document 724 31/12/2011
724 Document 724 31/12/2011

This is the finished code and it does send the emails:
DECLARE @rc int
DECLARE @SubjectMessage varchar(50)
DECLARE @ActioneeEmail varchar(100)
DECLARE @cmd varchar(200)
SELECT up.ActionId, up.Description AS ActionDescription,up.PlannedCompletionDate, PersonnelId, FirstName, LastName, EmailAddress INTO #temp
FROM UserFile uf
INNER JOIN UserPosts up
ON up.ActioneeId = uf.personnelId
WHERE up.PlannedCompletionDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
Group By
uf.FirstName, uf.LastName, uf.EmailAddress,
up.ActionId, up.Description, up.PlannedCompletionDate,
uf.PersonnelId
Order By
uf.PersonnelId

-- Select * from #temp

DECLARE @PersonnelId int
SELECT @PersonnelId = MIN(PersonnelId)
FROM #temp
WHILE @PersonnelId IS NOT NULL
BEGIN
SELECT
ActionId,
ActionDescription,
CAST (CONVERT(varchar(10), PlannedCompletionDate, 103) AS VarChar)As 'PlannedCompletionDate',
PersonnelId,
FirstName,
LastName,
EmailAddress
INTO #temp2
FROM #Temp
WHERE PersonnelId=@PersonnelId
Order By ActionId
-- Select * from #temp2 --Take out after testing
--End --Take out after testing
--DROP TABLE #Temp2 --Take out after testing
--DROP TABLE #Temp --Take out after testing

DECLARE @body VARCHAR(7000)
declare @ActionID varchar(10)
declare @PlannedCompletionDate varchar(15)
declare @ActionDescription varchar(100)
declare @FirstName varchar(30)
declare @LastName varchar(30)
declare @EmailAddress varchar(100)
declare _Cursor cursor for
select ActionId, ActionDescription,PlannedCompletionDate,PersonnelId, FirstName, LastName, EmailAddress
from #temp2

open _cursor
fetch next from _cursor into @ActionId, @ActionDescription,@PlannedCompletionDate,@PersonnelId, @FirstName, @LastName, @EmailAddress
Set @body = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">'
+ '<html xmlns="http://www.w3.org/1999/xhtml">'
+ '<head>'
+ '<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />'
+ '<title>Overdue Emails</title>'
+ '</head>'
+ '<HTML>'
+ '<BODY>'
+ 'You are logged as the owner for the documents listed below'
+ 'These documents are nearing their planned completion dates'
+ '<table width="500px" border="1" cellpadding="0" cellspacing="0" bordercolor="#000000">'
+ '<tr>'
+ '<td width="10%"> Action Id</td>'
+ '<td width="70%"> Description</td>'
+ '<td width="20%"> Planned Completion Date</td>'
+ '</tr>'
+ '<tr>'
+ '<td> ' + @ActionID + '</td>'
+ '<td> ' + @ActionDescription + '</td>'
+ '<td> ' + @PlannedCompletionDate + '</td>'
+ '</tr>'
while @@fetch_status = 0
begin
fetch next from _cursor into @ActionId, @ActionDescription,@PlannedCompletionDate,@PersonnelId, @FirstName, @LastName, @EmailAddress
Set @body = @body + '<tr>'
+ '<td> ' + @ActionID + '</td>'
+ '<td> ' + @ActionDescription + '</td>'
+ '<td> ' + @PlannedCompletionDate + '</td>'
+ '</tr>'
End
Set @body = @body +'</table>'
+ '</BODY>'
+ '</HTML>'
close _cursor
deallocate _cursor
Set @SubjectMessage = @EmailAddress
EXEC @rc = msdb.dbo.sp_send_dbmail
@from_address = N'Steve@mycompany.com',
@recipients = N'steve@myEmail.Com',
@blind_copy_recipients = N'steve@myEmail2.Com',
@subject = @SubjectMessage,
@body = @body,
@body_format = N'html';
SELECT RC = @rc
DROP TABLE #Temp2
SELECT @PersonnelId =MIN(PersonnelId)
FROM #Temp
WHERE PersonnelId > @PersonnelId
END
DROP TABLE #Temp

Can you see anything that would make the last row be fetched again?

Once again, thanks for your help.

Best Regards,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 11:23:16

the fetch next statement should be as last line of cursor loop. sequence should be like

@body concatenation first
sp_send_Dbmail call
fetch next

didnt understand why you need outer cursor though!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-02-13 : 11:41:40

Hi Visakh,

Works perfect, Thankyou

<< didnt understand why you need outer cursor though!

Me neither, just kept trying things until I got the result I wanted.

Great end to the day.

Best Regards,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 11:56:57

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-02-13 : 12:24:34

Hi Visakh,

Seems I might have jumped the gun a bit.
After I moved the fetch statement to the last line of the cursor loop, I thought it had worked because when I looked at the bottom of the email the double record had gone. It is actually now at the top.
So I get duplicate records at the top of the list now instead of the bottom.

Any ideas?

All the best,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 12:27:41

quote:
Originally posted by Steve2106

Hi Visakh,

Seems I might have jumped the gun a bit.
After I moved the fetch statement to the last line of the cursor loop, I thought it had worked because when I looked at the bottom of the email the double record had gone. It is actually now at the top.
So I get duplicate records at the top of the list now instead of the bottom.

Any ideas?

All the best,

Steve

did you remove the unnecessary first cursor. i cant understand reason of using it. you can do that bit also inside your main cursor loop

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-02-13 : 14:01:17

Hi Visakh,

In the code I last posted, which lines are you reffering to as I can only see 1 cursor.

I would appreciate it if you could delete the lines I do not need I would then have a better understanding of what you mean.

Thanks for you patience.

Best Regards,

Steve

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources