Problem with Charindex function

Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/

Problem with BULK INSERT ASCII file into nvarchar column

Hi,

I have a problem with BULK INSERT. I created the following table:

Code Snippet

create table Test
(id char(4), name nvarchar(16), last char(1))

I am trying to bulk insert data from ASCII (not unicode) file with only two rows:

0011First name
0018Second name

Since it is a fixed length file, I am using the following format file:

Code Snippet

8.0
3
1 SQLCHAR 0 4 "" 1 ID HEBREW_CI_AS
2 SQLCHAR 0 16 "" 2 NAME HEBREW_CI_AS
3 SQLCHAR 0 0 "\r\n" 3 Last HEBREW_CI_AS

With bcp utility everything works just fine!

Code Snippet

bcp Demo.dbo.test in c:\test -T -f c:\test.fmt

But when I use BULK INSERT in the following form:

Code Snippet

BULK INSERT Test FROM 'c:\Test'
WITH
(
FORMATFILE='c:\Test.fmt',
CODEPAGE='OEM'
);

I am getting error

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 2 (name).

Now, one interesting thing: if I change the name field from nvarchar to varchar, it is working with BULK INSERT as well.

Can anybody explain what is going on here?

I am using MS SQL 2000 and MSDE

Thanks in advance,

Eugene.

Another thing is that if I set the format file to specify row delimiter for that nvarchar field, it will also work.

Code Snippet

8.0
2
1 SQLCHAR 0 4 "" 1 ID HEBREW_CI_AS
2 SQLCHAR 0 16 "\r\n" 2 NAME HEBREW_CI_AS

But then in the real system i can't have multiple fields within the file...

|||

On SQL2005 the problem does not exist! Then it seems like a bug in SQL2000!

sql

Problem When I Try To Send More then 4000 To Database

Hi

I have Problem When I try To Send More Then 4000 char To SQL Server Database !!!

I have Create Windows Application that Send data To SQL Server Database in Web Server .. But When I try To Send More Then 4000 char to (nText) Flied The data damaged

And also in the Remote database I cant Create Filed of (nVarChar) data type with length more then 4000 char

In another word
I cant Store More then 4000 char in filed of (nText) data type
And I cant Create (nVarChar) filed with more then 4000 char Length

Where is the problem ??
And How can I solve it

Thanks with my regardingAn NVarchar field in SQL 7/2000 cannot be longer than 4000 (+- some) because the total size of any row in a SQL Server database cannot exceed 8196 (as I recall). nText fields do not count against that value, because they are stored on a seperate data page.

Please show the code (including any stored procedure) to try and insert the data.|||for your windows app problem, make sure you have an up to date mdac.
i had some odd problems years ago that were resolved by an mdac update.

for your 4000 character nvarchar problem,

varchar fields are actually limited to 8000 bytes (not characters)
(total row size of all populated varchars cannot exceed 8192)

for an nvarchar, the same 8000 byte limit applies.
since the n types are double byte, 4000 characters = 8000 bytes|||I think you have Datatype mixing problem you are sending ASCII to Unicode column, change the CHAR to NCHAR because in SQL Server 2000 you can Unicode enable per table and Column then run the code a again. I am assuming you know NTEXT is not Table Row based SQL Server creates an Arithmetic pointer to the data. Hope this helps.

Kind regards,
Gift Peddie|||Thanks for all replies

I have solve my problem

It is when I send the data to SQL Server I don't explicitly provide the data type of the filed in C# Code .. this is my problem.

The wrong code
SQLCommand.Parameters.Add("@.fld_Name", ValueObject);

the correct code
SqlParameter pr = new SqlParameter("@.fld_ Name", SqlDbType.NText);
pr.Value = ValueObject;
SQLCommand.Parameters.Add(pr);

It's simply like that

And I am very thanks for all reply

Fraas