Problem with formatting with CR and LF in a string

Hi,

I'm trying to use carriage return (CR) and line feed (LF) to format a string for use with the msdb.dbo.sp_send_dbmail stored procedure. My goal is to have several lines of text delineated with CR LF. However, it appears that SQL Server 2005 is replacing the CR LF with 2 spaces. The snipped below demonstrates this.

declare @.msg nVarChar(100)
set @.msg = 'this is before the cr ' + char(10) + char(11) + char(12) + char(13) + char(14) + char(10) + 'and this is after the cr'
select @.msg

If I copy the text returned by the select into a hex editor what I see for the portion of the string "cr ' + char(10) + char(11) + char(12) + char(13) + char(14) + char(10) + 'a" is:

63 72 20 20 0B 0C 20 0E 0F 61

I was expecting:

63 72 20 0A 0B 0C 0D 0E 20 61

This is what leads me to believe the CR and LF are being replaced with spaces as it shows hex 20 (SPACE) instead of hex 0A (LF) and hex 0D (CR).

Can someone explain to me how to make this do what I want it to?

Thanks
John

Given this simple test:

CREATE TABLE dbo.CRLF(id int,crlf nvarchar(100))

DECLARE @.msg nVarChar(100)

SET @.msg = 'this is before the cr ' + char(10)+ char(13) +'and this is after the cr'

INSERT INTO dbo.CRLF(id,crlf)VALUES(1, @.msg )

SELECT * FROM dbo.CRLF

I get this if I copy the results from Query Editor:

idcrlf

1this is before the crand this is after the cr

And this ouput put if I Open Table in Object Explorer:

1this is before the cr

and this is after the cr

|||I'm unable to duplicate your results. I get a single line when opening the table in the Object Explorer.

If you add a DECLARE @.LongMsg and set @.LongMsg = @.Msg + @.Msg + @.Msg and then use @.LongMsg as the @.body parameter of the msdb.dbo.sp_send_dbmail your email will be a single long line with no CR LF showing in the text. This is basically what I'm trying to do.

Perhaps there is a server setting somewhere that needs to be tweaked on our server.

John|||

Ya it true but you see 2 box kind of symbol when you opened

the table trough Object explore, select the option “Result to Text” in the

Query panel and select the row then output shows like this.

(1 row(s)

affected)

idcrlf

--

1This is 1 msg

and this is

2 msg

this is 3

msg

(1 row(s)

affected)

|||Ok, after changing the option I can see the CR in the results. Thanks for showing me something I didn't know.

However, I'm still having problems with the CR in the email text. Here's another snippit from the stored procedure:

Declare @.Message varchar(200)
Declare @.MessageList varChar(max)

SET @.MessageList = 'Daily Summary Report' + @.CR
SET @.MessageList = @.MessageList+'New Items'+ @.CR
SET @.MessageList = @.MessageList+'--'+ @.CR
DECLARE c2 CURSOR FOR
select MSG from Email.Messages
where Event_ID = @.EventID
OPEN c2
FETCH NEXT FROM c2
INTO @.Message
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.MessageList = @.MessageList + @.Message + @.CR
FETCH NEXT FROM c2
INTO @.Message
END
CLOSE c2
DEALLOCATE c2

This builds the body text of the email in the variable @.MessageList. It always puts a CR at the end of the line for the SET @.MessageList lines that are outside of the loop.

Inside the loop, if the message type has messages like "Added UPC 123456123456" it works fine with a CR at the end of every line.

If the message type has messages like "FAILED Client1 20050122 Source TYPE 2: 12 COLUMNS SEMI COLON DELIMITED production" it does not put a CR at the end of each line. If I put CR twice at the end of the line I do get two CR's in the email.

I'm stumped. At this point I have put in an if statement to put 2 CR's at the end of the event types that are not gettting the single CR and that seems to work, but I'm one of those types that like to understand why things work or don't work.

Any more thoughts? And thanks for your help.

John|||

Hmmm, I tried deleting a post I just made in this thread, because it seemed no longer relevant after John had made a simultaneous post, and it appears to have deleted John's new post, too.

I think that is a bug in the FORUM software, since I chose only my post when I selected "Delete".

Sorry!

Dan