Sql Server: storing text and keeping newlines?

Question

Hi, I suppose this question is kinda easy to answer, but I can't seem to figure it out.

I'm trying to store text (email bodies) into a nvarchar(max) column, but when I go read it, the newlines are all gone. At first I thought this might be some encoding/collation problem, but it doesn't seem to be the case.

I've searched around and haven't seen questions regarding this behavior, so I must be doing something really dumb but I can't figure it out.

The only way I've managed to keep the format of the text was to convert it into byte[] and storing it in a varbinary column, but then I lose all searching capabilities!

Edit: Oh, I forgot to mention that I'm working with c# and sqlserver2005.

Answer 1

Can you post the code that you're using for storing and retrieving? If you're using an @parameter it should "just work" so something "odd" is certainly going on :-)

Answer 2

"newlines" are not removed by SQL Server on insert or select. Indeed, no characters are removed (except where collation dependent etc)

When you read in SSMS grid view (eg right-click, open table) then they will not be seen.

You may also not see them in the Query panes because text is truncated (results to text mode) or you are in results to grid mode.

How do you know CRs/LFs are being removed?