(text) datatype question <Long Text> 2

[lothadio]

Where do I find the file that (text) datatype points to? I want to make sure that my insert query is inserting all of the data requested to.

******************problem******************************

I am inserting data into MSSQL SERVER 2000 Enterprise with PHP4/HTML scripts. The data is around 16,000 characters. However when I SELECT the data from the data base I am not getting all 16,000+ characters out of the database. I am getting 4022 characters. I need to see if this is a MSSQL SERVER problem or a PHP4/HTML problem. However I con not seem to find where the data is physically located. All I see in the Database field is <Long Text>

My understanding is that (text) datatype points to a file where the data is actually located, not in the database itself

Thanks

Christopher

 

[tlbroadbent]

Large text values are not stored in different files but rather on different pages in the database with their own structure. The following information comes from SQL Books Online. I suggest you read &quot;Managing ntext, text, and image Data&quot; in SQl Books Online for more information.

&quot;In Microsoft SQL Server 2000, users can enable a text in row option on a table so it could store text, ntext, or image data in its data row... Unless the text in row option is specified, text, ntext, or image strings are stored outside a data row; only the text pointers to these strings reside in the data rows. Text pointers point to the root node of a tree built of internal pointers that map to the pages in which string fragments (of text, ntext, and image data) are actually stored.

&quot;If an ntext, text, and image data value is no longer than a Unicode, character, or binary string (4,000 characters, 8,000 characters, 8,000 bytes respectively), the value can be referenced in SELECT, UPDATE, and INSERT statements much the same way as the smaller data types... When the ntext, text, and image data values get larger, however, they must be handled on a block-by-block basis. Both Transact-SQL and the database APIs contain functions that allow applications to work with ntext, text, and image data block by block.&quot; Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[lothadio]

dosent answer my question

 

[tlbroadbent]

I believe it does answer your questions. Of course, if you don't read BOL, you won't have the whole answer.

Question 1: Where do I find the file that (text) datatype points to?
Answer: There is no separate file. The data is stored in the database on separate pages from the other data rows. The other data rows point to the text pages - unless you've set the &quot;text in row&quot; option ON in SQL 2000. Earlier versions do not have that option.

Question 2: Is this an MSSQL Server problem? (implied from &quot;I need to see if this is a MSSQL SERVER problem or a PHP4/HTML problem.&quot
Answer: It is the way SQL Server works - by design. You will need to take special steps in your code.

Question 3: (implied) How can I store and read more that 4000 characters of data?
Answer: Read the documentation. Text fields require special handling that is explained therein. Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[cdukes]

You will find that if you are using Query Analyser to select the data from the database, it will only return you a certain number of characters. If you goto connection options/Advanced there is a maximum characters per column which you can set to more.

Alternatively, you can use

SELECT DATALENGTH(MyText) from tblName

which will give you the exact number of characters in the column.

AS Terry has said:
When the ntext, text, and image data values get larger, however, they must be handled on a block-by-block basis.

You can use READTEXT() to read the text in the column

Hope this helps,

Chris Dukes