Writing VERY large strings to SQL

[REB0RN]

I need to do 2 things (I don't like the idea of doing it, however sometimes you're told what to do, not asked)

1) Write all html content pages to SQL. The problem with doing so is that VarChar can only hold so much. I've tried using the image datatype, however have had no luck. I've did an insert into the table passing the string into image datatype, which does not give me an error, however when I do a SELECT on it, it gives me 0X134223...

2) Once these pages have been loaded to SQL, I need to be able to read these back into the browser so the page will display. I've tried using Response.ContentType = "plain/text", with Response.BinaryWrite from the previous value of 0X134223...etc however came up null when I looped through the recordset.

Anybody have any additional ideas, maybe something else besides the image or binary datatypes.

-REBORN

 

[LV]

Have you tried to use "text" datatype? We save some long text, including HTML, saved in fields of this datatype(SQL Server).

 

[Veep]

Use TEXT datatype. I've had to do this quite a bit and haven't had any problems. I just retrieve the HTML segments from the database, assign them to variables and response.write the variable.

 

[REB0RN]

When I try to add to this field, it is limited in how many characters it can store (contrary to what I read). Is there a datatype that doesn't have a limit? I need to store large documents in SQL. Some of these can be 10 pages long.

Again, I know this not the way to go about this, however, it is not my choice.

-REBORN

 

[LV]

Taken from SQL Server help

ntext:
Variable-length Unicode data with a maximum length of 230 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered.

text:
Variable-length non-Unicode data in the code page of the server and with a maximum length of 231-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes.

Should be long enough, I guess.

 

[Tarwn]

There is no unlimited field. Each record in SQL Server has a size limit (contrary to common belief), I think it is somehting like 8000 bytes. Once you try to insert something that will force a record over this magic number it does ....
Thats right, nothing So helpful. It once took me a week tio figure this out because I had an application that was strogin large quantities of html in a field (similar story) and I kept trying to insert this one record and it wopuldn't work. Finally I tried simply pasting the data into the datbase and thats when I got the strange error about maximum size of records being 8k (ish).

You really need to go back and explain in a kind way that this is not a bright idea. First of all you have the extra overhead of requesting it from the db/the db sending it to you/the recordset allocating memory, then you response.write.
It would be a much better method to simply put it in include files or something like that.
Storing content and presentation layer info like that in the db rarely makes sense. Even articles and such should be stored with minimal formatting (spans and divs and lists) and then rendreded using a CSS stylesheet.
I haveyet to see a logical reason for storing a website in a database. All your doing is adding overhead ot the CPu, increasing load time, increase the difficulty to edit the pages,etc etc

Anways, sorry if I kind of took off on that one, I must still be a little sore about it

-Tarwn

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}

http://www.tiernok.com/

The never-completed website

 

[Tarwn]

Oh, and yes I know this doesn't make sense.
I mean you are allowed to have fields that way exceed this. I have never understood this 8k row limit, and apparently it's not an option it's a fixed setting.

After looking it up, the actual number is 8,060 Bytes per row.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp

I assume that when they say Bytes per column that can't mean one entry in a field because this is obviously over the Bytes per row number, the odd thing is that the maximum column size for text and ntext is still way below the maximum database size, so they must be assuming a whole lot of tables in the database.

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}

http://www.tiernok.com/

The never-completed website