problem with field size

[DvDvDvDv]

Hi all.

I need to put large text in a database field. When I try to use text data type or even varchar data type, the database only allow me to insertt 1024 characteres, even if I write the text directly on the database manager.

My problem is: Microsoft says I can store large text (up to 2 bilion char) in a text field and it doesn´t happens to me. I'm using SQL server 2000.

What is worng ????

My real problem is I need to store texts in a field, some of them with 1 milion chars. How can I do that ???

Thanks in advance

Dv

 

[SQLBill]

This is from the BOL:

Using text in row to Store text, ntext, and image Values

Usually, text, ntext, or image strings are large (a maximum of 2GB) character or binary strings stored outside a data row. The data row contains only a 16-byte text pointer that points to the root node of a tree built of internal pointers that map the pages in which the string fragments are stored.

With Microsoft SQL Server, you can store small to medium text, ntext, and image values in a data row, thereby increasing the speed of queries accessing these values.

When the text, ntext, or image string is stored in the data row, SQL Server does not have to access a separate page or set of pages to read or write the string. This makes reading and writing the text, ntext, or image in-row strings about as fast as reading or writing varchar, nvarchar, or varbinary strings.

To store text, ntext, or image strings in the data row, enable the text in row option using the sp_tableoption stored procedure.

sp_tableoption N'MyTable', 'text in row', 'ON'

Optionally, you can specify a maximum limit, from 24 through 7000 bytes, for the length of a text, ntext, and image string stored in a data row:

sp_tableoption N'MyTable', 'text in row', '1000'

If you specify 'ON' instead of a specific limit, the limit defaults to 256 bytes. This default value provides most of the performance benefits: It is large enough to ensure that small strings and the root text pointers can be stored in the rows but not so large that it decreases the rows per page enough to affect performance.


SO the big points here are that text is normally stored OUTSIDE of a data row. And if stored in a data row, it can only be a max of 7000 characters.

-SQLBill

 

[db999]

Why do you think the field is only 1024 bytes? Have you checked you query analyser Maximum Characters per column setting (Tools:Options:Results). This may be restricting the number of characters you are returning and can view.

 

[DvDvDvDv]

Well.

I know I only can introduce 1024 characteres because I introduced them manually and the SQL Manager doesn't allow me to insert more.

Now I have my field with Text and 256 Bytes (using the text in row). I still only can insert 1024 characteres manually but my problem is:

What are the steps now to import a text from a text file, with more than 650.000 characteres ??? What is the best way ?

Dv

 

[db999]

There are many ways to insert to a text field.
I would probably use ADO/VB to update the field passing the text from the file to a stored procedure variable.
If you want to do the import through SQL then you might want to use BULK INSERT or Data Transformation Services (DTS). The options depend on how often you want to import text, and who is going to be the user to begin the process.

 

[SQLSister]

Text, ntext and image fields need special handling. Look at the writetext adn update text commands in Books Online.

 

[DvDvDvDv]

I'll try and let you know the results

Thanks

Dv