Text or Varchar types too small?
I am currently finishing up a project in sql server 2000 which is designed to send mass HTML e-mails via a web interface. The user is required to enter the message body (with an html formatting tool for colors, fonts, etc) and pick an HTML template to put around it.
The template is stored in the database as two varchar(8000) fields, one called TemplateTop, and one called TemplateBottom. Sticking the user-entered text in between the two makes up the text of the e-mail. Other fields in this table are a bigint for an ID, and a "subject" field, varchar(256).
Sounds simple, but I ran into problems when trying to make a new template by manually inserting HTML text into the TemplateTop and TemplateBottom fields. I realize that there is going to be a limit to the amount of text that can be stored in a field, but 8000 characters is MORE than enough for my needs. When copying and pasting into one of these fields (via Enterprise Manager), it takes only about the first 900 characters and gives me the error:
The value you entered is not consistent with the data type or length of the column, or over grid buffer limit.
Call me crazy, but wouldn't you think a varchar field with a size of 8000 would accept 8000 characters before giving an error like this? I had read elsewhere that the maximum "returned row" size is something like 8060 characters... so I adjusted TemplateTop to 2000, TemplateBottom to 1000 (for a total of about 3500 chars per line for this table), and got the exact same error, regardless of how small or large the other fields in the table were.
Evidently, the text type is supposed to be able to be used for very very very large amounts of text that will never have to be searched (which is applicable in this situation). Changing from varchar(8000) to "text" data types does absolutely nothing to solve this problem. I am not quite sure why.
So far, the only workaround that I have come up with is to make several varchar fields and combine them with the front end. This solution feels rediculous and counter-intuitive. Not only does this not feel like a good solution, I am ready to be done coding this project and don't want to have to spend several more hours making changes to what is already finished.
The total character count of the entire template, both top and bottom, is 3700 chars. I would imagine that this would not have to be split up across 5 varchar fields.
Anyone have a clue as to what is going on here?
|
