SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Member
    Join Date
    Jan 2006
    Location
    America
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    nText Data Type (being truncated)

    I have a question about the nText data type. I have one field that I need to store a lot of text and from what MSDN said it looks like nText is the correct data type to use. I use C# (SqlClient) to execute a stored procedure on the database, and verified that the procedure, database table, and C# code all have the type set to nText. However, when I recall the data using Query Analyzer its only reading back 256 characters from a 4,000 character field. I went into Enterprise Manager and its showing the field data as <long text>. I cannot find any errors in any of my code.

    Can someone tell me if there are any reasons why only 256 characters are being pulled back, or is this just a limit on how many characters Query Analyzer will return before truncating the rest of the data? I'm suck on wether this is a bug in my code, or if its just Query Analyzer that's dosen't bother returing all the data in a field that large.

    Thank you.

  2. #2
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would advice you to change your datatype to varchar(8000) or (4000)

    Text datatype always has this disadvantage, esp if you insert into a webpage

    Afrika

  3. #3
    SitePoint Member
    Join Date
    Jan 2006
    Location
    America
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I will consider changing the data type after I make sure that varchar will be large enough to hold all the data. Do you know why this happens? Why is the data being truncated? Am I correct that if I use a variable data type such as varchar, or varbinary, that the field is sized to the size of the data? For example, if I have a varchar(8000) and only have 5 characters saved in the field it will only use varchar(5)?

  4. #4
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    dont use varbinary. It wont work

    use varchar(8000)

    Doesnt not matter how much you save either 5 or 8000, but take note 8000 is the limit

    I really cant remember why text and ntext do that, but i learnt long ago not to use them.

    Afrika

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i think it has something to do wiht needing to retrieve ntext values with a special API command, but i don't know what it is.

    also, read this: http://www.eggheadcafe.com/PrintSear...asp?LINKID=309

  6. #6
    SitePoint Member
    Join Date
    Jan 2006
    Location
    America
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by afrika
    dont use varbinary. It wont work

    use varchar(8000)

    Doesnt not matter how much you save either 5 or 8000, but take note 8000 is the limit

    I really cant remember why text and ntext do that, but i learnt long ago not to use them.

    Afrika
    Yes, for this case it wont work. Its for saving byte[] data. This I already know. But I guess my wording was not the best on what my question was. What I wanted to know was if the varchar will take up the same amount of memory no matter how much data is stored in it, 5 or 8000. Or if it will size automatically so that it only uses the correct amount of memory based upon the amount of data stored in it.

    Thanks again!

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    found it, see the bottom of this page: http://msdn2.microsoft.com/en-us/library/ms191262.aspx

  8. #8
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nice read Long neck

  9. #9
    SitePoint Member
    Join Date
    Jan 2006
    Location
    America
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all the help. I have modified my data type to varchar(8000) and now I receive the following error message from Query Analyzer when I execute the script to create the tables.

    Warning: The table 'post' has been created but its maximum row size (8353) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
    What does this error mean? I have changed all the data sizes to 8000. Do I need to create a seperate table just to hold this field in its going to have large values?

    Thanks once again.

  10. #10
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    it's talking about the WHOLE row. overall, the whole row can not be more than 8060 bytes apparently.

  11. #11
    SitePoint Member
    Join Date
    Jan 2006
    Location
    America
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So it looks like if I want a varchar(8000) I have to move it to a seperate table in a row all by itself, then link that table to the first one.

    Is this still an issue with SQL 2005 since the varchar and nvarchar data types have been modified to support 2 GB max?

  12. #12
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i have no idea. but if you're storing something that big, i think the best approach is to get the ntext functions to work. just because the server CAN handle something in a particular fashion doesn't mean that you should use it that way.

  13. #13
    SitePoint Member
    Join Date
    Jan 2006
    Location
    America
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I'm not sure because from what I read in MSDN the nText and Text data types aren't going to be supported anymore in future SQL versions (because they resized the varchar and nvarchar data types to support 2GB or something). I think they took them out in 2005 but I'm not for sure.

    If that's the case then nText is the wrong approach because I'll just have more to modify in future versions.

  14. #14
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    oh, ok. mssql is not my forte.

  15. #15
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another way of handling large amount of data, is to put them in a html page and save the url in column.

  16. #16
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,629
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Rustic
    Well I'm not sure because from what I read in MSDN the nText and Text data types aren't going to be supported anymore in future SQL versions (because they resized the varchar and nvarchar data types to support 2GB or something). I think they took them out in 2005 but I'm not for sure.

    If that's the case then nText is the wrong approach because I'll just have more to modify in future versions.
    The long text data types are in SQL 2005 and work quite well as far as I can tell. Knowing the way MS works, they are not going to drop support of the column types for a long time as they are quite useful. Not to mention dropping the column types will break so many legacy applications that it would be very counterproductive.

    I think the problem here is that you are not using the ReaderOption.SequentialAccess (or something like that) to pull the data. Pull the data correctly and all of this should go away. I have never seen a varchar(8000) column that was not hacked text column.


Bookmarks

Posting Permissions

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