We help IT Professionals succeed at work.

MS SQL 2005 insert huge text file as one object

Hi Experts,

The question is how do I insert a very huge lets say 2-3MB file as one object into MS SQL 2005 column ?
Is it realistic ? Because I cannot see any data types [except BLOBs] > 8000 bytes :)
BULK is not an option because it just I think insert line as one separate row which does not fit into my
project specs. If I go with BLOB how do I do it ?

Thanks.


Comment
Watch Question

Top Expert 2006

Commented:
In SQL Server 2005, you can use the new varchar(MAX) type which supports up to 2 GB of text.

Author

Commented:
Is there link at microsoft that may prove it ?
I can't find it.
Top Expert 2006

Commented:
"Microsoft SQL Server 2005 introduces the max specifier. This specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types. You can use the large-value data types to store up to 2^31-1 bytes of data."

Using Large-Value Data Types (SQL Server 2005 Books Online)
http://msdn2.microsoft.com/en-us/library/ms178158.aspx
Top Expert 2006

Commented:
You can also read about it here: http://www.teratrax.com/articles/varchar_max.html

Author

Commented:
Hi 1701,

I see note there : When the sp_tableoption stored procedure 'large value types out of row' option is set to OFF, the in-row storage limit for large-value types is 8,000 bytes. A 16-byte root is stored in-row when this option is set to ON.

So I assume that it has to be set in DB properties. I looked but found nothing like this because its for stored procedure. Does MS SQL 2005 sets by default to accept 2GB in one column ?

Thanks.
Top Expert 2006
Commented:
Whether the data is stored in-row or out-of-row will not make a difference; the varchar(MAX) column will be accessible exactly as it was any other varchar column, except it will support larger amounts of text.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.