Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Unanswered: NText field in SQL??

    Hello All,

    Maybe a stupid question but I'm new to the db admin work so please bear with me.

    I've imported an Access db into SQL, in the Access db the field type was 'memo' to accomodate the large amount of text (on avg ruffly 4100 chars. with spaces). Now in SQL the field in the table I have set up as an ntext field, which I understood to be equivalent to a memo field in Access.

    My problem is when saving data to the field the first time it saves all the data correctly with the exception of the field in question. The data in the field is '<LongText>', now when I try to update the data in the table I get a 'Data Truncated' error message and no update takes place throughout the table.

    After testing this and trying different things, I've found that if I shorten this one field and try to save to the db I still get the 'Data Truncated' error message. If I shorten the data in the field AND delete the record from the SQL table then it will save just fine from there on out (which won't work for the reports).

    I'm not sure what I'm missing here to get this to work the way it did in Access.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What are you using to "edit" the data?

  3. #3
    Join Date
    Jan 2004
    Posts
    4
    The company uses a front end program that saves info to a flat file. When saving the flat file the db saves the information that it's looking for from the file.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    How is the data being inserted/updated in sql server ?

  5. #5
    Join Date
    Dec 2003
    Posts
    454
    In Sql server, there is another data type which is equivalent to a memo data type in Access. That is VARCHAR. If you can sure that the character number for the field is less than 8000, it is better to use VARCHAR as your data type instead of TEXT, NTEXT.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    good point gyuan - this is also one of the most common mistakes i see in databases - using a text datatype when varchar would suffice. But Jonathan, you need to validate the requirements for that field. text datatypes present performance and maintainence issues as well.

  7. #7
    Join Date
    Jan 2004
    Posts
    4
    I'm not sure what you mean by validate the requirements. I think your talking about making sure that I'm trying to use the right field for the data that I'm trying to put in it. If so, then the only data that is going in the field is alphanumeric characters. Is that what your talking about?

    I tried to change the field type to VarChar and change the length to 4000 (the max) and I get a 'string right truncated' error message which is telling me the same thing. To much data for the field to hold (I thought it could hold 8000?, I'm missing something there).

    The way the data is being inserted into the db is through the ODBC, the program they use is coded to use specific entries in the ODBC which then passes the data to the appropriate db in SQL.

  8. #8
    Join Date
    Nov 2003
    Location
    Victoria, Canada
    Posts
    3
    I was having a similar problem today and came across this post. I tried using VARCHAR as suggested entering a field length of 8000, but the data got truncated after 255 characters.

    gyuan, did you mean nvarchar or varchar?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Char and Varchar can handle 8000. NChar and NVarchar can only handle 4000.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and it could be that your interface is only displaying the first 255 characters.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Also check the setting for Maximum field width. The default in QA is 255. In addition, check the @@textsize global variable (select @@textsize) and if it's insufficient, increase it by SET TEXTSIZE <number>.

  12. #12
    Join Date
    Dec 2003
    Posts
    454
    Originally posted by devwilcat
    I was having a similar problem today and came across this post. I tried using VARCHAR as suggested entering a field length of 8000, but the data got truncated after 255 characters.

    gyuan, did you mean nvarchar or varchar?
    It is varchar which can hold 8000 characters. Since TEXT field can hold more than 8000 characters, you will get the following warning:

    Warning: Data may be lost converting column 'MyField' from 'text'.

    This means if there is a string with more than 8000 characters in this column, data may be lost when you convert TEXT to VARCHAR.

    When you do a SELECT query on Query Analyzer, you only can get the first 256 characters if data have more than 256 characters for VARCHAR, TEXT or NTEXT. That does not mean data got truncated after 256 characters.

    In the safe case, it is better to backup the table before doing that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •