SQL2008 Email formatting - 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)

SQL2008 Email formatting

Author

Topic

RajJol
Starting Member

17 Posts

Posted - 2010-10-15 : 09:17:53

Hi,

I have the below code which outputs an email from my SQL Server to my email address from the query embedded, however, the email outputs the correct results but the formatting is a mess. Is there a way I am able to format the output into columns or a table of some sort??

Thank You.

STATEMENT

declare @MyErrorCount int

select @MyErrorCount = Count(*) from Players
where InsertDate > CONVERT(CHAR(10),GETDATE(), 120)
and DataInsertDate < CONVERT(CHAR(10),GETDATE(), 120)

if @MyErrorCount > 0
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Player Email',
@recipients = 'abc@hotmail.com',
@subject = 'Some tables not updated',
@body = 'tables have not been updated',
@query = 'select tablename,datediff(d,datainsertdate,getdate()) ''Days Old'' from fotball.dbo.players where InsertDate > CONVERT(CHAR(10),GETDATE(), 120) and DataInsertDate < CONVERT(CHAR(10),GETDATE(), 120)'
Else
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Player Email',
@recipients = 'abc@hotmail.com',
@subject = 'All tables have been updated',
@body = 'All tables have been updated'

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-10-16 : 11:34:29

There is an example in BOL that formats a dataset as an html table. This might work for you. Please see "C. Sending an HTML e-mail message" at the bottom of this page:

[url]http://msdn.microsoft.com/en-us/library/ms190307.aspx[/url]

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-10-16 : 11:36:49

Also, Ive used this to format emails sent to smartphones and the html is not always parsed correctly. In those cases the attached queryoutput txt file was much more useful.

Probably has more to do with the phone's deficiencies than the method, but just fyi.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources