How to add carriage return

[sqlcasey]

Hi,

I am using db mail. I would like the body portion to have carriage returns, such as:

BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'me@yahoo.com',
@subject = 'FAILED JOB,
@body = 'Restart attempted...' + [carriage return here] + 'please check job status' ;
END

Need the correct syntax for [carriage return here]

Thanks!!

 

[Catadmin]

Actually, you don't concate it in at all.

Just something like:

BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'me@yahoo.com',
@subject = 'FAILED JOB,
@body = 'Restart attempted...


Please check job status' ;
END

That will leave two lines between your first line and second line. Just encompass the whole thing in a single pair of single quotes.

Make sense?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

You can also user Char(13) + Char(10), like this...

@body = 'Restart attempted...' + Char(13) + Char(10) + 'please check job status' ;


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

another way

declare @body varchar(500)
select @body = 'Restart attempted...' + char(10) + char(13) + 'please check job status' ;

select @body


view result in text to verify it's correct

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

I've never used 'dbmail', but in a script CHAR(10) + CHAR(13) are the ANSII characters for Return and New Line. Together they make up a carriage return.

-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

Shouldn't it be Char([!]13[/!]) + Char([!]10[/!])

http://msdn2.microsoft.com/en-us/library/ms187323.aspx

This might make a nice little interview question.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

true 13 + 10 will give you 2 lines

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

Interesting. I just tested that and something else:

SELECT 'Test' + CHAR(10) + CHAR(13) + 'Test'
Returns:
Test

Test

SELECT 'Test' + CHAR(13) + CHAR(10) + 'Test'
Returns:
Test
Test

AND...SELECT 'Test' + CHAR(13) + 'Test'
Returns:
Test
Test

So it appears we don't need to use the CHAR(10). Or am I missing something?

-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

To get a true Cr Lf, you need to use Char(13) + Char(10)

Without the 10, it may appear ok in a text file, but if you plan on processing this file in another application that expects to see a true CR/LF, then you'd better have the 10.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

SELECT 'Test' + CHAR(13) + CHAR(10) + 'Test'
SELECT 'Test' + CHAR(13) + 'Test'

look identical in EditPlus when I do view-->linebreaks


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Try this...

Right click in QA -> restults in text

Run the first one (with the Char(10)), then copy/paste the results to notepad.

Then, run the second one and copy/paste to notepad.

You'll see that the one with the char(10) correctly displays on 2 lines, while the one without it displays a square box between the words.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

I think the only place it might make a difference is with an application that immediately sends it to paper. It 'could' cause the lines to print over each other. But that's rare these days (I can't think of anything that would do that).

I guess the bottom line is 'better safe than sorry' and just include both.

-SQLBill

Posting advice: FAQ481-4875

 

[sqlcasey]

For some reason the mail didn't like the char 10 and 13 but this worked:

BEGIN
declare @bodytext varchar(255)
set @bodytext = 'Restart attempted...' + Char(13) + Char(10) + 'please check job status'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients= 'me@yahoo.com.com',
@subject = 'carriage return test',
@body = @bodytext;
END