| Author |
Topic |
|
juanita
Starting Member
9 Posts |
 Posted - 2009-03-18 : 11:48:53
|
| Well, I want to insert some columns in a table of length 4000. But I get error and the size of the row. I've tried and I've seen, the lengths of each data type (nText, text, nvarchar, varchar, and varbinary to). The text does not let me resize. And the best definitions comes Varchar is, but when inserting 2 or more columns of 4000 characters I get this error: "Unable to create a row of size 12,019, higher than the maximum allowable 8060. I have read in various places that the only solution is to build the table, there is no other way? Thanks |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-03-18 : 12:23:51
|
| Hi,1) How normalised is the table? -- If it is at all. Most wide table schemas are in direct opposition to a normalised approach -- Assuming that there is a primary key then you can extend your table with another with a foreign key to that primary. You can expand your columns that wayHow possible is it to redesign the schema at this point?2) Are you using 2005? if so the TEXT data-types are deprecated and you should use NVARCHAR(MAX) / VARCHAR(MAX) insteadWithout knowing your schema and more of what you are trying to do I don't think you are going to get much helpful advice.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
juanita
Starting Member
9 Posts |
Posted - 2009-03-18 : 12:40:37
|
| thanks I am using Sql 2000 and the data type nvarchar (4000), I get error. Any advice? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-03-19 : 06:21:35
|
| Could you post your table definition and state which extra columns you want to put on it.Did you ignore my question about (1) (Normalisation) for any particular reason?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
juanita
Starting Member
9 Posts |
Posted - 2009-03-23 : 06:25:21
|
| The table definition as follows:"CREATE TABLE [dbo].[Proyecto]( [Cod] [int] IDENTITY(1,1) NOT NULL, [AvanceMercado] [nvarchar](4000) COLLATE Modern_Spanish_CI_AS NULL, [AvanceEquipoPromotor] [nvarchar](4000) COLLATE Modern_Spanish_CI_AS NULL, [AvanceProductoServicio] [nvarchar](4000) COLLATE Modern_Spanish_CI_AS NULL, [AreaFinanciera] [nvarchar](4000) COLLATE Modern_Spanish_CI_AS NULL, [AreaLegal] [nvarchar](4000) COLLATE Modern_Spanish_CI_AS NULL, [Formacion] [nvarchar](4000) COLLATE Modern_Spanish_CI_AS NULL, [Recursos] [nvarchar](4000) COLLATE Modern_Spanish_CI_AS NULL, [Cumplimiento] [nvarchar](4000) COLLATE Modern_Spanish_CI_AS NULL, [Caracteristicas] [nvarchar](4000) COLLATE Modern_Spanish_CI_AS NULL, [MedioAmbiente] [nvarchar](4000) COLLATE Modern_Spanish_CI_AS NULL, [Tutor] [nvarchar](4000) COLLATE Modern_Spanish_CI_AS NULL, [CodDafo] [int] NULL, [CodPrevisionProyecto] [int] NULL, [CodCierreProyecto] [int] NULL, [CodRevisionProyecto] [int] NULL, [CodMedioAmbiente] [int] NULL, [CodOtrosProgramas] [int] NULL, CONSTRAINT [PK_Proyecto_1] PRIMARY KEY CLUSTERED ( [Cod] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]"Can any property of the table to get row size does not exceed 8060 bytes? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-03-23 : 07:59:31
|
| Well at least you've got a primary key. (COD) Was there nothing else in the table you could guarantee would be unique?I think you should make up some more tables and move your NVARCHAR(4000) columns into there. Then reference the Proyecto table with a FOREIGN KEY.Maybe you should read this?http://en.wikipedia.org/wiki/Database_normalizationCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
juanita
Starting Member
9 Posts |
Posted - 2009-03-23 : 08:24:19
|
| Thanks But my tables and my database is normalized, and I do not think it has anything to do with expanding the size of the row on the properties of the table. I think the topic of normalization which I propose to create another table and relations with the foreign key. No? The solution I've proposed, but I do not like and I think there must be q best solutions: properties or table row? |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-23 : 08:40:21
|
| If you are using SQL Server 2000 then if you use the datatype text then the data will be stored seperately from the row which will store only a pointer allowing you to exceed the data page limit of 8060. Alternatively you could try replacing nvarchar(4000) with varchar(4000) (unless you need to store unicode characters) this should halve the storage used. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-23 : 10:05:29
|
| If possible, you could convert to SQL Server 2005 or 2008 where this will not be a problem.CODO ERGO SUM |
|
|
|
juanita
Starting Member
9 Posts |
Posted - 2009-03-24 : 04:54:59
|
| No it is not possible, the client can not support SQL Server 2005. I'm doing tests with the data type text, but I cut the string. And varchar will not let me insert more than 8060 characters. How many bytes equals one character? |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-24 : 05:18:57
|
| The Row Size Limit is 8060 bytesvarchar is a single-byte character and nvarchar isa double-byte character. |
|
|
|
|
insert some columns in a table of length 4000