I would try using a true CR+LF pair, e.g.

CHAR(13)+CHAR(10)

--
Aaron Bertrand, SQL Server MVP
[url]http://www.aspfaq.com/[/url]

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.


"Raj Parulekar (Hotmail)" <rparulekarhotmail.com> wrote in message
news:uZAYDwkPDHA.704tk2msftngp13.phx.gbl...
> Thank you Uri. You are correct that the syntax does give the intended
result
> when viewed as a text result, but my concern is whether it will show the
> line break when the field is used as a data source for a report. To test
> this I linked this table to an Acces 2000 db and created a report based on
> it and in the report I was not able to see the line break - instead the
two
> lines were separated by a square character. In fact I see the same square
> character between the two intended lines when I try to view the data via
> Enterprise Manager.
>
> Just to make sure that we can store data with line breaks in SQL Server, I
> manually entered data into the field and separated the lines by using a
> combination of the shift+enter keys, which worked, and I was able to see
> this line break in the report and also in EM.
>
> So the questions then that come to mind are -
> 1) Why does manually entering data using shift+enter to insert line breaks
> show up in EM and also on the report, but it is not the case when
> programmatically entering the data using CHAR(10)?
> 2) Is there a way to programmatically emulate the shift+enter keys in SQL
so
> the line break will show up in EM and also on the report.
>
> Thanks for your help!!!
>
> Raj
>
>
> "Uri Dimant" <uridiscar.co.il> wrote in message
> news:eRghWwgPDHA.3192TK2MSFTNGP10.phx.gbl...
> > Raj
> > I got this right result
> >
> > CREATE TABLE #W
> > (
> > COL VARCHAR(100)
> > )
> >
> > ---INSERT INTO #W VALUES ('A'+CHAR(13) +'N')
> > INSERT INTO #W VALUES('12/11/2002 - Spoke to Tom' + CHAR(13) +
> '12/5/2002 -
> > Spoke to Tim')
> >
> > SELECT * FROM #W
> > DROP TABLE #W
> >
> > COL
> > 12/11/2002 - Spoke to Tom
> > 12/5/2002 - Spoke to Tim
> >
> >
> > "Raj Parulekar (Hotmail)" <rparulekarhotmail.com> wrote in message
> > news:eFcosGgPDHA.1748TK2MSFTNGP11.phx.gbl...
> > > How would I be able to insert data into a field with line breaks in
it.
> > >
> > > e.g. I wante to enter the following in the notes field of a table
> > >
> > > "12/11/2002 - Spoke to Tom"
> > > <line break>
> > > "12/5/2003 - Spoke to Tim"
> > >
> > > I want the data in the field to appear as two separate lines if I were
> to
> > > view it in a report. I would like to know the syntax in SQL to do this
> if
> > > possible. I have tried the following:
> > >
> > > INSERT INTO TABLE1
> > > VALUES
> > > ('12/11/2002 - Spoke to Tom' + CHAR(10) + CHAR(13) + '12/5/2002 -
Spoke
> to
> > > Tim')
> > >
> > > but this did not work - it entered the data into the field but it
> appeared
> > > on the same line with two square characters in between (I guess SQL
> Server
> > > did not understand the two CHAR commands). Just to make sure I viewed
> the
> > > data in an Access report but I still saw two squares.
> > >
> > > Does anyone know how to do what I am asking in SQL? (I am willing to
use
> a
> > > cursor to do this).
> > >
> > > Thanks.
> > >
> > > Raj
> > >
> > >
> > >
> > >
> >
> >
>
>