Formatting @query results using xp_sendmail

[Terri]

I know formatting should be handled in the client app and not in SQL Server
but this is the situation I must develop in so any help would be
appreciated.

I'm running the following in Query Analyzer

USE Pubs
DECLARE @MessageSubject VARCHAR(50)
SELECT @MessageSubject = 'Report'
EXEC master.dbo.xp_s endmail 'me@mine.com',
@query = 'SELECT au_fname, au_lname from pubs.dbo.author s',
@subject = @MessageSubject

The results in my email look like:

au_fname au_lname
------------------- ----------------------------------------
Abraham Bennet
Reginald Blotchet-Halls
Cheryl Carson

I would like the results to be like:

First Name: Abraham
Last Name: Bennet

First Name: Reginald
Last Name: Blotchet-Halls

First Name: Cheryl
Last Name: Carson

Thanks

[Simon Hayes]

Re: Formatting @query results using xp_sendmail


"Terri" <Terri@spamaway .com> wrote in message
news:c0u48d$tn2 $1@reader2.nmix .net...[color=blue]
> I know formatting should be handled in the client app and not in SQL[/color]
Server[color=blue]
> but this is the situation I must develop in so any help would be
> appreciated.
>
> I'm running the following in Query Analyzer
>
> USE Pubs
> DECLARE @MessageSubject VARCHAR(50)
> SELECT @MessageSubject = 'Report'
> EXEC master.dbo.xp_s endmail 'me@mine.com',
> @query = 'SELECT au_fname, au_lname from pubs.dbo.author s',
> @subject = @MessageSubject
>
> The results in my email look like:
>
> au_fname au_lname
> ------------------- ----------------------------------------
> Abraham Bennet
> Reginald Blotchet-Halls
> Cheryl Carson
>
> I would like the results to be like:
>
> First Name: Abraham
> Last Name: Bennet
>
> First Name: Reginald
> Last Name: Blotchet-Halls
>
> First Name: Cheryl
> Last Name: Carson
>
> Thanks
>
>
>[/color]

See CHAR() in Books Online - you could try something like this:

select
'First Name: ' + au_fname + char(13) + 'Last Name: ' + au_lname +
char(13) + char(13)
from
pubs.dbo.author s

Simon

[Terri]

Re: Formatting @query results using xp_sendmail


"Simon Hayes" <sql@hayes.ch > wrote in message
news:403292f4$1 _2@news.bluewin .ch...[color=blue]
>
> See CHAR() in Books Online - you could try something like this:
>
> select
> 'First Name: ' + au_fname + char(13) + 'Last Name: ' + au_lname +
> char(13) + char(13)
> from
> pubs.dbo.author s[/color]

This works, thanks.
@query = 'select ''First Name: '' + au_fname + char(13) + ''LastName: '' +
au_lname + char(13) from pubs.dbo.author s',