Added line feed in insert statement

Question

I have an sql script I'm running in Management studio.  It is inserting the following statement.

INSERT INTO [dbo].[ldescrip] ([id], [description]) VALUES (1303, 'number of current sample

')

When I run this from my management studio session it works.  When the dbas run it from their management studio session it puts in a ^M at the end of the description.  I mail the dbas the sql script as an attachment using Outlook.  It's not outlook because when I open the attachment and run it in management studio it again works for me. 

Can anyone think of what could be the difference between our two setups.

Lisa

---

lg

Answer 1

Hi lg !

You may get the desired output using below query;

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
INSERT INTO [dbo].[ldescrip] ([id], [description]) VALUES (1303, 'number of current sample' + @NewLineChar + ' A')

The problem might be due to change in Collation. Also try to have a look at below article by Pinal Dave;

http://blog.sqlauthority.com/2009/07/01/sql-server-difference-between-line-feed-n-and-carriage-return-r-t-sql-new-line-char/

Please let me know if this helps. Hopefully i have answered you correctly.

Thanks, Hasham

Answer 2

The problem is I don't want the newline char.  It wasn't in my original file and I don't want it in the database either.  I don't want \r\n, only \n.  For some reason when the dbas run it \r\n gets put in.  When I run it only \n gets put in which is what I want.  I'll check with the dbas about the collation.

---

lg