SEO

#1 (**permalink**) 02-28-2008, 08:02 PM

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_sendmail 'me@mine.com',
@query = 'SELECT au_fname, au_lname from pubs.dbo.authors',
@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

#2 (**permalink**) 02-28-2008, 08:02 PM

"Terri" <Terri@spamaway.com> wrote in message
news:c0u48d$tn2$1@reader2.nmix.net...
> 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_sendmail 'me@mine.com',
> @query = 'SELECT au_fname, au_lname from pubs.dbo.authors',
> @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
>
>
>

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.authors

Simon

#3 (**permalink**) 02-28-2008, 08:02 PM

"Simon Hayes" <sql@hayes.ch> wrote in message
news:403292f4$1_2@news.bluewin.ch...
>
> 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.authors

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Formatting @query results using xp_sendmail