Format/Syntax for looping thriugh SP for email

[Imakeoil]

Hi,
I am trying to send an email everyday that list down events by equipment. I am trying to display all the rows returned out of a temp table. I have no idea what I am doing, so I have been trying to copy a few of the other auto emails that are already set up, but don't have a good enough understanding to read/interpret them and then apply it to my email.

So far I have:

SELECT * FROM #t_end

IF ((SELECT COUNT(number) FROM #t_end) > 0)
BEGIN

DECLARE @From varchar(1000),
@To varchar(1000),
@Subject varchar(1000),
@MessageBody varchar(5000),
@Cc varchar(1000),
@oldRow int

SET @oldRow = (SELECT TOP 1 number FROM #t_end)
SET @From = 'test@anycompany.com'
SET @To = imakeoil@anycompany.com'
SET @Subject= 'Test'
--SET @Cc =
SET @MessageBody = ''


SET @MessageBody = @MessageBody +
+ CHAR(13) + ' ' + CHAR(13) + 'Down Stats' + char(13)
+ 'Eqmt' + char(9) + 'Start' + char(9) + 'End' + char(9) + 'Duration' + char(9) + 'Reason' + char(9)+ 'Name' + char(9) + 'Comment' + char(13)

WHILE @oldRow <= (SELECT MAX(number) FROM #t_end)

BEGIN
--SET @MessageBody = @MessageBody
--+ CAST(ltrim(rtrim((SELECT eqmt FROM #t_end WHERE number = @oldRow))) AS varchar)
--+ char(9) + char(9)
--+ left(convert(varchar(50),convert(datetime,(SELECT start FROM #t_end WHERE number = @oldrow)),1),len(convert(varchar(50),convert(datetime,(SELECT start FROM #t_end WHERE number = @oldrow)),1)) - 3)
--+ char(9)


SET @oldRow = (SELECT TOP 1 number FROM #t_end WHERE number > @oldRow)

END
PRINT @MessageBody
EXEC sp_SMTPemail @From, @To, --@Cc,
@Subject, @MessageBody
END


This does email out the headings, however nothing I try will add the rows returned by the query.

I am hoping the email will show:

Down Stats
Eqmt Start End Duration Reason Name Comment
Ex01 0845 0855 10 101 Engine Knocking
Tr33 1256 1456 120 353 Hoist NULL

Can someone please point me in the direction of some good material to learn how these emails are formatted? (Or send me an answer. I have spent a few hours on google and am not seeing anything that even remotely looks like the queries we are using for the other emails.

I appreciate any help, Thanks!

 

[Crowley16]

sp_SMTPemail is that a custom rolled stored procedure to send e-mails? I've not come across it before...

normally, I would use sp_send_dbmail which is a built in part of the sql server 2005 db mail system. On 2000 it would be sp_sendmail.

this has built in options to include data from a query in your mail message.

here's the msdn page for it...

--------------------
Procrastinate Now!

 

[Imakeoil]

It looks like sp_SMTPemail is a custom SP. I can't find sp_sendmail in any of my db's, unfortunately, since it looks easier to use.

 

[Imakeoil]

Here is the solution:

SET @MessageBody = @MessageBody +
LEFT((SELECT [name] FROM PowerView.dbo.hist_exproot WHERE shiftindex = @tempstart),@char) + ' to ' + LEFT((SELECT [name] FROM PowerView.dbo.hist_exproot WHERE shiftindex = @endd),21) + CHAR(13) + ' ' + CHAR(13) +
+ CHAR(13) + ' ' + CHAR(13) + 'Down Stats' + char(13) + CHAR(13)
+ 'Eqmt ' + 'Start' + ' ' + 'End' + char(9)+ ' '
+ 'Minutes' + char(9) + ' ' + 'Name' + char(9) + char(9) + char(9) + char(9) + char(9) + 'Comment' + char(13) + CHAR(13)

WHILE @oldRow <= (SELECT MAX(number) FROM #t_end)

BEGIN

SET @MessageBody = @MessageBody +
+ (SELECT RTRIM (eqmt) from #t_end where number = @oldRow) + char(9)
+ (SELECT RTRIM (start) from #t_end where number = @oldRow) + char(9)
+ (SELECT RTRIM ([End]) from #t_end where number = @oldRow) + char(9)
+ (SELECT RTRIM (Duration) from #t_end where number = @oldRow) + char(9) + ' '
+ (SELECT ([Name]) from #t_end where number = @oldRow) + char(9)
+ (SELECT (Comment) from #t_end where number = @oldRow)
+ CHAR(13) + CHAR(10)
--+ CHAR(13) + CHAR(10)

SET @oldRow = (SELECT TOP 1 number FROM #t_end WHERE number > @oldRow)

END
PRINT @MessageBody

 

[drlex]

I found that structuring the emails in html solves the formatting issue. It does mean a lot of code either side of the SQL query using a 'SELECT ... FOR XML PATH', but the users like the mails, so it works well for me.

soi là, soi carré

 

[SQLBill]

Crowley16 had a typo...it is xp_sendmail in SS2000. It is part of the SQL Server install and can be found in the Master database.

Look up xp_sendmail in the SS2000 Books Online for details on how to use it.

In SS2005, it is sp_send_dbmail (not available in SS2005 Express edition)

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Crowley16]

oh yeah...

it shows I've not used 2k for a while...

--------------------
Procrastinate Now!