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.


LVL 1
fpoyavoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Expert1701Connect With a Mentor 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.
0
 
Expert1701Commented:
In SQL Server 2005, you can use the new varchar(MAX) type which supports up to 2 GB of text.
0
 
fpoyavoAuthor Commented:
Is there link at microsoft that may prove it ?
I can't find it.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Expert1701Commented:
"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
0
 
Expert1701Commented:
You can also read about it here: http://www.teratrax.com/articles/varchar_max.html
0
 
fpoyavoAuthor 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.
0
All Courses

From novice to tech pro — start learning today.