Been doing some research after getting this error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Cannot create a row of size 8297 which is greater than the allowable maximum of 8060.
And realised that my nice responsive varchars had a maximum total size. so I changed them for 'slower' text data types. but my DB still won't allow any more input.
has the limit been reached now regardless of what I change or can I rebuild the DB to recover the space or something?First, what version are you on? 2000 or 2005?
Second, post the DDL for your tables with the 8000 byte columns.|||That message is only a warning. The table is still created, I believe.
EDIT: Unless you are actually trying to stuff that much data in a row. Then it becomes an error.|||That message is only a warning. The table is still created, I believe.
EDIT: Unless you are actually trying to stuff that much data in a row. Then it becomes an error.
True enough...but it can't be a good design|||well you get no prizes for guessing it's not the best designed DB in the world, I basically said that at the top. But it has been going fine for two years delivering words to a huge web site, right up to the point where it filled up.
so I changed some data types from varchar to text in the hope of freeing up some space but it still seems to be full.
so back to my Q: is it possible that I need to do something else to free up the space or is it something els?
I think the DB is 2000 (2005 doesn't suffer from this same varchar limit does it?)
here's the DDL - could well be all wrong, but that's why I'm here.
[id] int IDENTITY(1, 1) NOT NULL,
[dest_url] varchar(32),
[country] varchar(32),
[continent] varchar(16),
[intro] varchar(2048),
[quote1] varchar(512),
[title1] varchar(512),
[body1] varchar(2048),
[quote2] varchar(512),
[title2] varchar(512),
[body2] varchar(2048),
[quote3] varchar(512),
[title3] varchar(64),
[body3] text,
[quote4] varchar(512),
[title4] varchar(64),
[body4] text,
[quote5] text,
[title5] varchar(64),
[body5] varchar(2048),
[quote6] varchar(512),
[image1] varchar(255),
[image2] varchar(255),
[image3] varchar(255),
[image4] varchar(255),
[image5] varchar(255),
[image6] varchar(255),
[submissionDate] smalldatetime,
[altTag1] varchar(255),
[altTag2] varchar(255),
[altTag3] varchar(255),
[altTag4] varchar(255),
[altTag5] varchar(255),
[altTag6] varchar(255),
I understood that text data types are slow and varchar is fast and only uses the space it needs but I suspect that is wrong.|||Holy denormalization, Batman!
Yeah, that is a mess and really needs to be normalized into subtables.
Let me guess...you can't change the schema, right?
Or you could upgrade to 2005 and use the new varchar(max) datatype.
But let me guess...upgrading is not an option right now, right?
The maximum data size of a record cannot exceed 8000 bytes (give or take). Replacing some of your columns with text datatypes should have resolved the issue for you, as the text pointers are only 16 bytes.
Post the DDL for your table after you changed the datatypes.|||So that's considered a big ugly table then? you live and learn, I thought SQL Server was immensely powerful and nothing I chucked at it would give it a problem
When I 'designed' it, it seemed like all the columns were not duplicated or needed anywhere else so should all go into one table.
I don't really know about normalisation, can it be done retrospectively (and link internally) or will I have to change all my ASP code to find all the different tables?
In any case, back to the original problem, is there a 'rebuild' option somewhere to reclaim empty space?
Seems my DB is V7! could be upgraded to 2000 (oooo wow, thanks a bunch Mr. host)|||you seem to have missed this crucial request of blindman's:
"Post the DDL for your table after you changed the datatypes."|||That table should really be three or 4 tables
Anytime you see a column with a name that ends in an incrementing #, thos solumns should be 1 column in a child table...not only is it a better design, it provides you with more flexibility in case you need more than n rows
You basically are making rows into columns in your design
So I guess, I guessed right|||Thanks Brett, you guessed right. I know nothing.
OK so normalising my table will also free up space because the records will be shorter, is that right?
But this table only holds data on a country page and the incrementing numbers just tell the page where to put the block of text. It seems massive overkill (in itself) to create a table for each column (if it has a #), it's basically unique data which isn't called up anywhere else. and wouldn't I have to do complex joins etc or at least new sql strings to create the entire page.
I don't really want to go back and rewrite the CMS code and it just seems wrong when I have a very simple database that just happens to have about 5000 chars of text for some countries.
Isn't this a fairly crappy limitation for an expensive 'enteprise level database' ?
I didn't repost my DDL because the first one WAS the new layout with the text fields put in to try and free up some space. They used to be varchar(2048).|||Splendid intro to database design & normalisation. I plan to work my way through his site myself.
Problem you are having is specificly with first normal form.
http://www.tonymarston.net/php-mysql/database-design.html
Also - even though you have introduced text if you total up all the VARCHARS you still well exceed 8000 bytes.|||Well, rewriting code would suck...BUT...you could normalize the data, THEN create a view that looks like your existing table, and name the view what the name of the existing table is...no rewrite needed
This is how I would have designed this...without really knowing what the app does
CREATE TABLE MAIN (
[Mainid] int IDENTITY(1, 1) NOT NULL,
[dest_url] varchar(32),
[country] varchar(32),
[continent] varchar(16),
[intro] varchar(2048),
[submissionDate] smalldatetime,
)
CREATE TABLE Body (
[BodyId] int,
[Mainid] int,
[quote] varchar(512),
[title] varchar(512),
[body] varchar(2048)
)
CREATE TABLE Images (
[ImageId] int,
[Mainid] int,
[image] varchar(255)
)
CREATE TABLE Tags (
[TagId] int,
[Mainid] int,
[altTag] varchar(255)
)|||OH, and just because there's an 8k limit is a physical limitation at the time because of the page size...they removed this in 2005
Your easiest path to a soultion really is an upgrade to 2k5
BUT..this is a very good example of how important data modeling is before app development|||excellent resource thanks.
but then the answer to my Q is yes I will need to rewrite my SQL to join all the split tables.
and No, it isn't really necessary to split up my table because the data is unique and only inputted once.
and yes, SQL Server 7 sucks and I need a new DB host.
And Yes, I can be a bit slow - I now see what is meant by the various limits and how all my varchars have stacked up.
ta|||OK so normalising my table will also free up space because the records will be shorter, is that right?Normalising will not free up space. In fact, it will likely require more "space", but the size of each individual record will be reduced and thus you will not be hitting the 8k data limit per row.
It seems massive overkill (in itself) to create a table for each column (if it has a #),You should create a subtable to hold the Quote, Title, and Body data.
You should create another subtable to hold image data.
You should create another subtable to hold altTag data.
If image and tag data are related, both should be stored in the same table.
What you call overkill, we call good design.
I don't really want to go back and rewrite the CMS code and it just seems wrong when I have a very simple database that just happens to have about 5000 chars of text for some countries.Lesson learned: design the database first. Write the application code second.
Isn't this a fairly crappy limitation for an expensive 'enteprise level database' ?Sorry, but your ignorance of the product is not a reflection upon its quality.
I didn't repost my DDL because the first one WAS the new layout with the text fields put in to try and free up some space. They used to be varchar(2048).So change the remaining varchar(2048) columns to text. The point is, you should be able to add up the maximum sizes of all your data columns and stay under 8k.|||and yes, SQL Server 7 sucks and I need a new DB host.No. It is your database design that sucks. SQL Server 7 could easily handle these application requirements with a properly designed schema. Stop blaming the software and/or hardware for your poor design.|||You should create a subtable to hold the Quote, Title, and Body data.
You should create another subtable to hold image data.
You should create another subtable to hold altTag data.
Brilliant
The message you enetered is too short|||No, it isn't really necessary to split up my table because the data is unique and only inputted once.No - it isn't necessary but also that isn't the definition of good design. I could put the universe into a single single table single column single row database but it would not be good design. Uniqueness is not enough.
Anyway - see how you get on with the article :)|||This is 7???
I thought M$ stop supporting that...or is that <7
Oh, and blind dude, you forgot a Main table|||EAV...EAV...EAV...
http://www.dbforums.com/showthread.php?t=1619660
and OTLT...OTLT...OTLT|||looks like our last two posts overlapped.
I now get it.
but if that limitation is acceptable why has MS changed it in V-2005?
if you have a moment for us newbies, Can you just clarify: if I was using 2005 or the correct data types for this amount of text is there any other reason to complicate the structure on my table?
as splitting a table of unique data only used on one page still seems like overkill even on a good design.|||Oh, and blind dude, you forgot a Main tableNo. I said he should create those tables. He already has a main table.
I could put the universe into a single single table single column single row database but it would not be good design.Using the XML datatype, I assume? I can just see one of my developers coming up with this design.|||as splitting a table of unique data only used on one page still seems like overkill even on a good design.The thing to remember is that normalisation principles are nothing to do with the size of datapages, or anything physical. They are methodologies designed to eliminate (or at least minimise) data modification anomolies. They also typically result in much more easily queried data structures.|||Using the XML datatype, I assume? I can just see one of my developers coming up with this design.
Funny you mentioned that...I just went through this..they pass me XML, but damned if I was gonna store this as XML
DECLARE @.idoc int
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.ReqData
CREATE TABLE #FutureRent (
[Year] [int] NOT NULL
, [NumMonths] [int] NOT NULL
, [NetRate] [money] NULL
, [OtherExp] [money] NULL
, [A_D] [money] NULL
, [FreeRent] [money] NULL
)
INSERT INTO #FutureRent (
[Year]
, [NumMonths]
, [NetRate]
, [OtherExp]
, [A_D]
, [FreeRent])
SELECT *
FROM OPENXML (@.idoc, '/FutureRent/Data',1)
WITH(
[Year] [int]
, [NumMonths] [int]
, [NetRate] [money]
, [OtherExp] [money]
, [A_D] [money]
, [FreeRent] [money]
)
IF EXISTS (SELECT * FROM FutureRent WHERE MEPRecID = @.MEPRecId)
UPDATE a SET
a.[NumMonths] = b.[NumMonths]
, a.[NetRate] = b.[NetRate]
, a.[OtherExp] = b.[OtherExp]
, a.[A_D] = b.[A_D]
, a.[FreeRent] = b.[FreeRent]
, a.[ModifiedBy] = @.APPUSER
, a.[ModifiedDate]= GetDate()
FROM FutureRent a
INNER JOIN #FutureRent b
ON a.[MEPRecID] = @.MEPRecID
AND a.[Year] = b.[Year]
ELSE
INSERT INTO FutureRent (
[MEPRecID]
, [Year]
, [NumMonths]
, [NetRate]
, [OtherExp]
, [A_D]
, [FreeRent]
, [CreatedBy]
, [CreateDate]
, [ModifiedBy]
, [ModifiedDate])
SELECT
@.MEPRecID
, [Year]
, [NumMonths]
, [NetRate]
, [OtherExp]
, [A_D]
, [FreeRent]
, @.APPUSER
, GetDate()
, @.APPUSER
, GetDate()
FROM #FutureRent|||Using the XML datatype, I assume? I can just see one of my developers coming up with this design.
better to base64 encode the xml and store the entire blob as varchar(max). that way you are "secure" too! do I win?
:)|||Jez, you are scaring me. One of the projects I am working on is doing exactly what you describe.|||haha! I guess you win then. :)
John R. wrote: