October 6, 2006 at 11:50 pm
Hi All,
I have tried to Send a mail using SQL Mail where I passed a query which will be the body of the mail. The mail was sent successfully but the formatting is very odd. My question is how can I format the text within the mail so that it looks clean. Is it possible to send mail using SQLMail in RichText/HTML format. Please help.
With warm regards,
Pradeep
October 9, 2006 at 2:31 am
I'm not sure what you can do in SQL mail.... we are using SMTP mail (EXEC master..xp_smtp_sendmail) which gives good opportunity to format the output. We use sp_makewebtask procedure to create HTML file from the result of query, and then insert it into the body of a message or (if it exceeds the 64k limit for HTML e-mail) send it as attachment.
October 11, 2006 at 1:15 pm
xp_smtp_sendmail works great on job. Does anyone know of a way to use it if fatal alerts arise in the SQL log?
Terry
October 23, 2006 at 2:57 pm
I can't find the xp_smpt_sendmail proc. Any ideas why?
Error message in Query Analyzer: Could not find stored procedure 'master.xp_smtp_sendmail'.
October 23, 2006 at 3:36 pm
You are using wrong name.
It must be master.dbo.xp_smtp_sendmail
And you need probably visit this site:
http://www.sqldev.net/xp/xpsmtp.htm
_____________
Code for TallyGenerator
October 24, 2006 at 7:09 am
Go to http://www.sqldev.net/xp/xpsmtp.htm It's a great freebie and does everything you could ask for, for the price!
Terry
October 27, 2006 at 12:49 am
I am using xp_smtp_sendmail , I am geting error
Error: sending message
Server response: 550 5.7.1 Unable to relay for bhushan683@gmail.com
I have set the relay for smtp server to 127.0.0.1 but it is still giving me error
EXEC master.dbo.xp_smtp_sendmail
@server = 'localhost',
@from = 'bhushan683@gmail.com',
@to = 'bhushan683@gmail.com',
@subject = 'HTML Testing...',
@type = 'text/html',
@message = N'<HTML><H1>this is some content for the body part object</H1></HTML>'
Anybody have suggestions on these please reply
October 27, 2006 at 7:16 am
I don't see any reference to the SMTP server. Is what's posted the entire script that you're trying to execute?
Terry
October 27, 2006 at 7:55 am
Yeah, that could be it... Bhushan, is the computer on which this SP runs configured as mail server? I guess you are launching that on your SQL Server... which probably isn't mailserver at the same time (also never tried using "localhost" here). Try changing this line to something like:
@server = N'COMPANY_MAIL_SERVER_NAME'
October 27, 2006 at 11:12 am
Try this way
Set @messageToSend= '<HTML><H1>this is some content for the body part object</H1></HTML>'
Execute @rc=master.dbo.xp_smtp_sendmail
@server = 'SERVERNAME',
@from = 'bhushan683@gmail.com',
@to = 'bhushan683@gmail.com',
@subject = 'HTML Testing...',
@type = 'text/html',
@message = @messageToSend
March 11, 2008 at 10:54 am
This example uses link servers to get a space report and email it in HTML format. You can use this as a starting block/template.
Hope you find it useful. If not drop another post and I'll fish out more examples for you to work with.
SET NOCOUNT ON
DECLARE @tableHTML NVARCHAR(MAX) ;
declare @sub varchar(200)
declare @table table
(servername varchar(50),
drive varchar(20),
free_space_mb varchar(20),
total_size_mb varchar(20),
free_space_percent varchar(20),
sample_date datetime)
select @sub = 'SQL Server Physical Space Report'+space(1)+convert(varchar(11), getdate(), 103)+space(1)+convert(varchar(11), getdate(), 108)
insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')
insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')
insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')
insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')
insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')
insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')
insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')
insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')
insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')
insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')
insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')
insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')
insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')
insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')
insert into @table select * from OPENQUERY([SV-XXXX],'select * from master .. QTX_FREE_SPACE')
SET @tableHTML =
N' ' +
N' ' +
N' ' +
N' ' +
N' '+
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
cast((
select td = servername, '',
td = upper(drive)+':\', '',
td = free_space_mb+space(1)+'MB' , '',
td = case when total_size_mb = '' then 'Mirror' when total_size_mb is null then 'Mirror' else total_size_mb+space(1)+'MB' end, '',
td = case when free_space_percent = '' then 'Mirror' when free_space_percent is null then 'Mirror' else free_space_percent+space(1)+'%' end, '',
td = convert(varchar(11), sample_date, 103)+space(1)+convert(varchar(11), sample_date, 108), ''
from @table
where convert(varchar(11), sample_date, 103) = convert(varchar(11), getdate(), 103)
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N' ' +
N'
' +
N' ' +
N'
' +
N' ' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'anyone',
@recipients = 'anyone@anywhere.com',
@subject = @sub,
@body = @tableHTML,
@body_format = 'HTML' ;
March 27, 2008 at 2:02 pm
davidw,
I appreciate you posting the code for writing out an email in HTML; however, The beginning of the line of code
AS NVARCHAR(MAX) ) +
got interpreted as a "winkie face" emoticon! ... can you please post the code in
(remove the spaces)
[ quote ]
[ / quote ]
brackets for us?
Thanks in advance,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply