Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2002
    Posts
    192

    Unanswered: SQL Server Text fields

    The Text datatype can hold several million characters
    The size of a record can be no more than 8 KB in normal data.

    Created a table with one text field. For some reason can only store 1K of data into it. Even if the 8K limit was a factor (which it shouldn't for a text field) I cannot add more than 1K of data into my text field. 1023 characters to be exact.

    Why is SQL Server 2000 doing this and more importantly how to resolve?

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by access_dude
    The Text datatype can hold several million characters
    The size of a record can be no more than 8 KB in normal data.

    Created a table with one text field. For some reason can only store 1K of data into it. Even if the 8K limit was a factor (which it shouldn't for a text field) I cannot add more than 1K of data into my text field. 1023 characters to be exact.

    Why is SQL Server 2000 doing this and more importantly how to resolve?
    Strange
    Text datatype is a variable-length datatype that can hold up to 2147483647 characters. This datatype is used when you want to store the character values with the total length more than 8000 bytes.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Just provide the table structure.Let me check whats going wrong..
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  4. #4
    Join Date
    Feb 2005
    Posts
    78
    Another thought, are you getting an error message when you store the data or are you making this diagnosis based on the data you are retrieving from the field.

  5. #5
    Join Date
    Mar 2002
    Posts
    192
    The table structure as follows:

    Table Name: T1
    Field 1: F1
    Data Type: Text
    Length: 4000
    Allow Nulls: false

    No defaults
    default collation
    no formulas

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by access_dude
    The table structure as follows:

    Table Name: T1
    Field 1: F1
    Data Type: Text
    Length: 4000
    Allow Nulls: false

    No defaults
    default collation
    no formulas

    when you specify a text you don't specify its length.I am really surprise to find it.It will not work at all I mean the table will not be created.Plz check your DLLs
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  7. #7
    Join Date
    Mar 2002
    Posts
    192
    good point. In the process of trying other configurations I mixed the settings.

    I tried with
    NVARCHAR 4000
    VARCHAR 8000
    TEXT 16 byte pointer

    Whatever's going on is weird looking further into this the varchars and nvarchars does not seem to allow more than their max characters. tried this on other servers and they seem to be doing this as well.

    tried on RTM, SP3

  8. #8
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by access_dude
    good point. In the process of trying other configurations I mixed the settings.

    I tried with
    NVARCHAR 4000
    VARCHAR 8000
    TEXT 16 byte pointer

    Whatever's going on is weird looking further into this the varchars and nvarchars does not seem to allow more than their max characters. tried this on other servers and they seem to be doing this as well.

    tried on RTM, SP3
    Well, I think when using with text,varchar and nvarchar in a single row it is found that they are not allowing charaters to their fullest capacity except text.When testing with text and varchar(8000) ,I found varchar(8000) is taking only 7902 characters max in a single row.
    Well, I think some gurus should comment on this....
    Last edited by rudra; 03-16-06 at 21:37.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  9. #9
    Join Date
    Mar 2002
    Posts
    192

    Figured out a way

    OK I figured it out.

    If you copy and paste value using E.M. you run into 1003 char limit
    If you manually type value using E.M. you run into 1003 char limit
    If you write an INSERT INTO statment it works fine!?!?

    Can anyone reproduce this on their servers? Either way, this is just weird!

Posting Permissions

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