Professional Web Applications Themes

Where is <long text> stored? - Microsoft SQL / MS SQL Server

I'm trying to insert several large fields in a single row and have resorted to using 'text' fields due to the 8060 byte constraint. When I query the results in Query yzer, they now display as <long text>. Where is the actual data being stored? Thanks....

  1. #1

    Default Where is <long text> stored?

    I'm trying to insert several large fields in a single row
    and have resorted to using 'text' fields due to the 8060
    byte constraint. When I query the results in Query
    yzer, they now display as <long text>. Where is the
    actual data being stored? Thanks.
    Mike Guest

  2. #2

    Default Re: Where is <long text> stored?

    I think it displays this message when you open table in Enterprise Manager.
    and click on "Return Rows" option. Since text datatype has a very huge
    storing capacity you can not see it in EE. The text datatype can store up to
    2GB worth of data, but such column's data is not stored inside the row
    itself. The row just contains a 16 bytes pointer that points to the text
    data

    Viewing TEXT data:

    Through SQL Server "query yzer" the maximum number of characters that
    can be returned is 8192. This setting can
    be done by going to

    Tools | Options | choose "Results" tab. Check the maximum character per
    column. if it is not 8192 then set it to 8192

    If your data is crossing this limit then you will have to make use of
    substring function as a workaround.
    First make sure your datalength is going beyond 8192 characters by typing
    following command.
    Ex:
    SELECT DataLength(TextColumn) FROM Text_table.

    Above query will return you the lengh of the text column for each row of the
    table.
    Now to view this data you can make use of SUBSTRING function as follows.

    Select substring(TextColumn, 1, 8000), substring(TextColumn, 8001, 8000)
    ,substring(TextColumn, 16001, 8000) from Text_Table

    OR another alternative is :

    use DTS/bcp to transfer the TEXT data to the flat file and view it.

    -Vishal

    "Mike" <com> wrote in message
    news:052a01c361f0$c5610120$gbl... 


    Vishal Guest

  3. #3

    Default Re: Where is <long text> stored?

    On Wed, 13 Aug 2003 16:15:22 -0700, "Mike" <com>
    wrote: 

    Other blocks in the mdf file.

    BOL sez the following and much more:

    The pages holding text, ntext, and image data are managed as a single
    unit for each table. All of the text, ntext, and image data for a
    table is stored in one collection of pages.

    ....

    ntext, text, and image Data When text in row Is Set to OFF

    The structure of the B-tree used to store text, ntext, or image data
    when the text in row option of sp_tableoption is set to OFF differs
    slightly if there is less than 32 KB of data than if there is more.

    If there is less than 32 KB of data, the 16-byte text pointer in the
    data row points to an 84-byte text root structure. This forms the root
    node of the B-tree structure. The root node points to the blocks of
    text, ntext, or image data.

    ....

    JXStern Guest

Similar Threads

  1. text area too long
    By sattman in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: September 27th, 11:53 AM
  2. CFMX 7 SQL TEXT FIELD Long Text Retrieval
    By eblackey101 in forum Macromedia ColdFusion
    Replies: 0
    Last Post: March 16th, 03:49 PM
  3. Text box with "Scroll Long Text" in properties check question
    By tommymurphy@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 7
    Last Post: June 24th, 07:15 PM
  4. Replies: 3
    Last Post: September 30th, 01:34 AM
  5. output jpg stored in database as long binary to asp: how?
    By bart plessers in forum ASP Components
    Replies: 1
    Last Post: September 30th, 12:41 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139