problem with line break from NVARCHAR field

I have data saved in nvarchar field in SQL Server and I am displaying it using <= dbReader["my_field"] but even the three or four paragraphs comes as one paragragh and I don't know know where are my line breaks going?

In SQL Server if you want line breaks and well formatted text you must save your text as word in an image column so you can just get the word file back. There could be other ways but this is easy so I use it you could get other answers. Hope this helps.

|||

but this will kill the database size!!

in ASP 3.0 I was doing the following so what is the simillar command in .NET?

<%= Replace(rs("full_story"), vbCrlf, "<br>") %>

|||You were using that against Access or SQL Server?|||SQL Server|||

I found three solutions all long but may do what you want but not with one line of code. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/odc_VSTWrdSql.asp

http://codebetter.com/blogs/john.papa/archive/2005/08/09/130476.aspx

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=151&rl=1

sql

Problem with LIKE %

I have a stored procedure:
CREATE PROCEDURE SearchHistoryClient
@.TextContain nvarchar(1000),
@.Email nvarchar(512),
@.Operator_URI nvarchar(512),
@.DateFrom DateTime,
@.DateTo DateTime,
@.Company_ID int
AS
DECLARE @.SQL nvarchar(4000)
SELECT @.SQL = '
SELECT
tblTemp.ChatTime,
tblTemp.Client_ID,
tblTemp.Client_SIPURI,
tblOperator.Operator_ID,
tblOperator.Operator_SIPURI,
tblOperator.Operator_Alias,
tblTemp.ChatTranscript
FROM
(SELECT * FROM tblSupportClient INNER JOIN tblClient ON
(tblSupportClient.ClientID = tblClient.Client_ID))
AS tblTemp INNER JOIN tblOperator ON tblTemp.Operator_ID =
tblOperator.Operator_ID
WHERE
(tblOperator.Company_ID = ''' + cast(@.Company_ID as nvarchar) + ''' ) AND
(tblTemp.ChatTime BETWEEN ''' + cast(@.DateFrom as nvarchar) + ''' AND
''' + cast(@.DateTo as nvarchar) + ''')
'
SELECT @.TextContain = '%'+ @.TextContain + '%';
-- Check if keyword is specified
IF (@.TextContain <> "")
BEGIN
SELECT @.SQL = @.SQL + ' AND (tblTemp.ChatTranscript LIKE ''' +
cast(@.TextContain as nvarchar) + ''') '
END
...
EXEC(@.SQL)
It always return 0 rows if @.TextContain contains more than a word.
e.g. if you call the procedure and the input string for @.TextContain is
"Hello"
then it returns some rows if they contains "Hello" string. But when you
specify "Hello John" it won't work correctly, though in your database the
string Hello John... exists.
Can anyone help me?Can you do a PRINT of @.SQL before executing, and see how the string is
formed? Btw, why are you using dynamic SQL?
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John C#" <John C#@.discussions.microsoft.com> wrote in message
news:66468268-7264-41A7-AD86-F6162639EA6F@.microsoft.com...
> I have a stored procedure:
> CREATE PROCEDURE SearchHistoryClient
> @.TextContain nvarchar(1000),
> @.Email nvarchar(512),
> @.Operator_URI nvarchar(512),
> @.DateFrom DateTime,
> @.DateTo DateTime,
> @.Company_ID int
> AS
> DECLARE @.SQL nvarchar(4000)
> SELECT @.SQL = '
> SELECT
> tblTemp.ChatTime,
> tblTemp.Client_ID,
> tblTemp.Client_SIPURI,
> tblOperator.Operator_ID,
> tblOperator.Operator_SIPURI,
> tblOperator.Operator_Alias,
> tblTemp.ChatTranscript
> FROM
> (SELECT * FROM tblSupportClient INNER JOIN tblClient ON
> (tblSupportClient.ClientID = tblClient.Client_ID))
> AS tblTemp INNER JOIN tblOperator ON tblTemp.Operator_ID =
> tblOperator.Operator_ID
> WHERE
> (tblOperator.Company_ID = ''' + cast(@.Company_ID as nvarchar) + ''' ) AND
> (tblTemp.ChatTime BETWEEN ''' + cast(@.DateFrom as nvarchar) + ''' AND
> ''' + cast(@.DateTo as nvarchar) + ''')
> '
> SELECT @.TextContain = '%'+ @.TextContain + '%';
> -- Check if keyword is specified
> IF (@.TextContain <> "")
> BEGIN
> SELECT @.SQL = @.SQL + ' AND (tblTemp.ChatTranscript LIKE ''' +
> cast(@.TextContain as nvarchar) + ''') '
> END
> ...
> EXEC(@.SQL)
> --
> It always return 0 rows if @.TextContain contains more than a word.
> e.g. if you call the procedure and the input string for @.TextContain is
> "Hello"
> then it returns some rows if they contains "Hello" string. But when you
> specify "Hello John" it won't work correctly, though in your database the
> string Hello John... exists.
> Can anyone help me?|||Hi,
I tried to create a new table with a field of ntext type and then I use SQL
command to select, it works. However, it still does not work with fields
marked <long text>. Is it about type error? What is the problem here?
"Narayana Vyas Kondreddi" wrote:

> Can you do a PRINT of @.SQL before executing, and see how the string is
> formed? Btw, why are you using dynamic SQL?
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "John C#" <John C#@.discussions.microsoft.com> wrote in message
> news:66468268-7264-41A7-AD86-F6162639EA6F@.microsoft.com...
>
>

problem with join

hi, I have two tables join with each other,
key from table1 is nvarchar column, key from table2 is varchar column, so
when they join, the performance is very bad, however, when I change both
them to varchar and join them together, the performance is much better.
but the problem is I don't want to change the column definition, I still
want to improve the query. is it possible?When column datatypes are different in comparison side, SQL Server has to
internally convert it for you and that's why your query is performing slower
.
This is by design and is the expected.
You can use CAST funtion and see if it helps which I highly doubt.
"Britney" wrote:

> hi, I have two tables join with each other,
> key from table1 is nvarchar column, key from table2 is varchar column, s
o
> when they join, the performance is very bad, however, when I change both
> them to varchar and join them together, the performance is much better.
> but the problem is I don't want to change the column definition, I still
> want to improve the query. is it possible?
>
>

problem with delete statement

Hi guys,

i need some help on this issue, i have a table with a nvarchar type column with primary key constraint. I have made a procedure which deletes record from this table using the primary key. My primary key is TxnID, and when i ran the following statement:

DELETE from mytable where TxnID = '119DA-117440520'

it deleted the record with the id '119DA-1174405208'

I dont want this to happen, i only want to delete records with the exact IDs that i provide. Can any one tell me how i should do that?

Thanks in advance.

sorry! i managed to fix it...actually the width was not defined properly which was causing problem

sorry again