SQL Mail format - 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 2000 Forums

SQL Server Development (2000)

SQL Mail format

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-09 : 08:06:37

David writes "is there a way to format the mail we have a query and it looks like:

LastName
FirstName
SSN#
ReferralDate
Hospital
AttendingPhysician
InsuranceType
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
-----------------------
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
Holsinger
David
010-55-5555
2003-10-08 00:00:00.000
PUH
test
Value Commercial

It's pretty ugly, is there a way to make it look neater.

The code is

ALTER PROCEDURE dbo.SP_EmailReferrals11a AS
declare @bSendMail int

select @bSendMail = count(*) from CandL.dbo.View_AllReferrals
BEGIN
Execute Master..XP_Sendmail
@recipients='removed',
@blind_copy_recipients="removed',
@Subject = '11:00 am Referrals Update',
@message = 'The following are the referrals from 9:00am this morning',
@query='select LastName, FirstName, SSN#, ReferralDate, Hospital, AttendingPhysician, InsuranceType from CandL.dbo.View_AllReferrals where (ReferralDate = CONVERT(varchar(10), getdate(), 101) and ReferralTime > right(DATEADD(hh,-2,getdate()),7))'

END

Thanks"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-09 : 12:19:10

Why not attach the results to the e-mail using @attach_results?

You might be able to play around with @width and @separator to make it look better.

Tara

ereader
Yak Posting Veteran

50 Posts

Posted - 2003-10-13 : 08:30:55

Well you can do it in a more professional way
by using sp_makewebtask which will create a html file by using the .tpl file and you can easily attach the file using the xp_sendmail send it to any destination with the format you want

Ex:
EXEC sp_makewebtask @outputfile = 'C:\SQL.html',
@query = "select * from pubs.dbo.authors",
@templatefile = 'C:\SQL.tpl'

EXEC master.dbo.xp_sendmail @recipients = @email,
@attachments = 'C:\SQl.html',@subject = 'Daily report (see attachment)',
@message = 'SQL TEAM - eReader'

======
Cheers

======

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources