String Truncation in a NVARCHAR field

Hi,

one of my fields in my database tables is of the data type NVARCHAR(4000).
I assign this field to be of the maximum length as I need to store text strings of various lengths
and some of these strings may contain characters like an 'e' with a stroke above (I do not know what you call that
in English).

I imported some data into the table and for the field of data type NVARCHAR(4000), the text information was
truncated at the 1000th character (inclusive of spaces). When I tried to enter some data directly into the field
using Enterprise Manager, I am unable to do so.

I hope someone can shed some light on this issue.

Regards,
CW
fullmfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee W, MVPTechnology and Business Process AdvisorCommented:
I think that's a limit of Enterprise manager.  I've had issues wiith it as well.  If you write a insert or update query in Query Analyzer, you can store more than 1000 characters.
amit1978Commented:
Let us know whats the error it is throwing.

As such there is no limit in inserting data in Enterprise Manager,
Limitation is totaly depend on size and type of Field.

Also is it possible any other person is accessing the same field(Might be there is an lok on the table).

Amit
Lee W, MVPTechnology and Business Process AdvisorCommented:
There may not technically be a limit, but I've encountered the same error in Enterprise Manager and inserted my data without issue using Query Analyzer.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

StephenCairnsCommented:
I've run into this same error. what version of SQL are you using?
I belive (and I cant remember where I found this) but there is a character max for a record in sqlserver2000. and its about 4000 chars
how many other characters are you inserting into the record?

possibly you could a different datatype??? or seperate table?

when I hit this problem we pushed it back to the users - if they want to write essays do it in word and attach the document

I guess this wont be much help

cheers
Anthony PerkinsCommented:
Two separate issues:

1.  Depending on the service pack there is a limit as to how many characters can be input in Enterprise Manager.  SP4 is 900 characters and SP3 is 1023.  Solution: Wean yourself from Enterprise Manager and use SQL Query Analyzer.

2. Unless you are using a double-byte language (such as Chinese) change nvarchar to varchar and you can then use up to 8000 bytes.  If you need more you may need to resort to text data type, but understand that there are limitations in functionality if you do that.
arbertCommented:
Agree with Acperkins--Enterprise manager was never meant to be used for a data entry tool...
StephenCairnsCommented:
acperkins, arbert are you talking about Enterprise manager or SQL Query analyzer?
I had this issue before (asp calling a stored proc), had the same issue calling the proc from the analyzer (or possibly the sql, it was a while ago)
oops just realised I said I found a  4000 char limit  I meant a 8000 char limit (4000 nvarchar)

I'm hoping I'm wrong
Cheers

Anthony PerkinsCommented:
>>acperkins, arbert are you talking about Enterprise manager or SQL Query analyzer?<<
I suspect if you re-read what we both said, the answer to that will become obvious.

If not, you should know that the data type length limit has nothing to do with the interface you are using, be it SQL Server Query Analyzer, Enterprise Manager, ADO, etc.
StephenCairnsCommented:
>>I suspect if you re-read what we both said, the answer to that will become obvious.<<
appologies, it is very obvious and i did miss it.

>>I imported some data into the table and for the field of data type NVARCHAR(4000), the text information was
truncated at the 1000th character (inclusive of spaces). When I tried to enter some data directly into the field
using Enterprise Manager, I am unable to do so.<<

When you imported what did you use?
I think possibly changing the datatype to text is worth a try.

cheers

amit1978Commented:
where is the auther........

there is no comments from auther.....whats the actual requirement of auther.

fullmfAuthor Commented:
Hi, I took some time to digest all the comments from you helpful souls
and try out the suggestions.

For starters,
I am using MS SQL Enterprise Edition with SP4 Updates installed.

I am using a program that was developed in-house to import a text file into the database.
I guess I should use DTS instead in order to rule out any possibility that the error is caused by the program.

I am using only English language but the accented characters are missing if the field is VARCHAR, they are imported properly
when the field is NVARCHAR.

The length of the characters vary from anyway to a few hundered characters to a 2,500 characters.
In the meantime, I am going to try importing the text file again using DTS and give the feedback later.

Thank you for responding to my query.

Regards,
CW

Anthony PerkinsCommented:
>>I am using only English language but the accented characters are missing if the field is VARCHAR, they are imported properly
when the field is NVARCHAR.<<
If you are talking about the accented characters, characters with a tilde on top, etc  you have in some Latin languages such as: á, é, í, ó, ú, ü, ç, ñ, etc.  You do NOT need to use NVarchar.
Anthony PerkinsCommented:
P.S.  You will not be able to edit more than 900 characters in Enterprise Manager.  The data is there you just cannot modify it, not even set it to Null.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.