Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060.

Posted by Lieven Cardoen on Stack Overflow See other posts from Stack Overflow or by Lieven Cardoen
Published on 2010-01-26T12:41:02Z Indexed on 2010/03/30 19:03 UTC
Read the original article Hit count: 611

Filed under:
|

I have already asked a question about this, but the problems keeps on hitting me ;-)

I have two tables that are identical. I want to add a xml column. In the first table this is no problem, but in the second table I get the sqlException (title). However, apart from the data in it, they are the same. So, can I get the sqlException because of data in the table?

I have also tried to store the field off page with

EXEC sp_tableoption 'dbo.PackageSessionNodesFinished', 
  'large value types out of row', 1

but without any succes. The same SqlException keeps coming.

First table: PackageSessionNodes

CREATE TABLE [dbo].[PackageSessionNodes](
    [PackageSessionNodeId] [int] IDENTITY(1,1) NOT NULL,
    [PackageSessionId] [int] NOT NULL,
    [TreeNodeId] [int] NOT NULL,
    [Duration] [int] NULL,
    [Score] [float] NOT NULL,
    [ScoreMax] [float] NOT NULL,
    [Interactions] [xml] NOT NULL,
    [BrainTeaser] [bit] NULL,
    [DateCreated] [datetime] NULL,
    [CompletionStatus] [int] NOT NULL,
    [ReducedScore] [float] NOT NULL,
    [ReducedScoreMax] [float] NOT NULL,
    [ContentInteractions] [xml] NOT NULL,
 CONSTRAINT [PK_PackageSessionNodes] PRIMARY KEY CLUSTERED 
(
    [PackageSessionNodeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Second table: PackageSessionNodesFinished

CREATE TABLE [dbo].[PackageSessionNodesFinished](
    [PackageSessionNodeFinishedId] [int] IDENTITY(1,1) NOT NULL,
    [PackageSessionId] [int] NOT NULL,
    [TreeNodeId] [int] NOT NULL,
    [Duration] [int] NULL,
    [Score] [float] NOT NULL,
    [ScoreMax] [float] NOT NULL,
    [Interactions] [xml] NOT NULL,
    [BrainTeaser] [bit] NULL,
    [DateCreated] [datetime] NULL,
    [CompletionStatus] [int] NOT NULL,
    [ReducedScore] [float] NOT NULL,
    [ReducedScoreMax] [float] NOT NULL,
    [ContentInteractions] [xml] NULL,
 CONSTRAINT [PK_PackageSessionNodesFinished] PRIMARY KEY CLUSTERED 
(
    [PackageSessionNodeFinishedId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

First script I tried to run (First two ALTER TABLE work fine, the third crashes on SqlException)

ALTER TABLE dbo.PackageSessionNodes ADD
    ContentInteractions xml NOT NULL CONSTRAINT 
    DF_PackageSessionNodes_ContentInteractions 
    DEFAULT (('<contentinteractions/>'));

ALTER TABLE dbo.PackageSessionNodes
    DROP CONSTRAINT DF_PackageSessionNodes_ContentInteractions

ALTER TABLE dbo.PackageSessionNodesFinished ADD
    ContentInteractions xml NOT NULL CONSTRAINT 
    DF_PackageSessionNodesFinished_ContentInteractions 
    DEFAULT (('<contentinteractions/>'));

ALTER TABLE dbo.PackageSessionNodesFinished
    DROP CONSTRAINT DF_PackageSessionNodesFinished_ContentInteractions

Second script I tried to run with the same result as previous script:

EXEC sp_tableoption 'dbo.PackageSessionNodes', 
    'large value types out of row', 1

ALTER TABLE dbo.PackageSessionNodes ADD
    ContentInteractions xml NOT NULL CONSTRAINT
    DF_PackageSessionNodes_ContentInteractions 
    DEFAULT (('<contentinteractions/>'));

ALTER TABLE dbo.PackageSessionNodes
    DROP CONSTRAINT DF_PackageSessionNodes_ContentInteractions

EXEC sp_tableoption 'dbo.PackageSessionNodesFinished', 
    'large value types out of row', 1

ALTER TABLE dbo.PackageSessionNodesFinished ADD
    ContentInteractions xml NOT NULL CONSTRAINT 
    DF_PackageSessionNodesFinished_ContentInteractions 
    DEFAULT (('<contentinteractions/>'));

ALTER TABLE dbo.PackageSessionNodesFinished
    DROP CONSTRAINT DF_PackageSessionNodesFinished_ContentInteractions

Now, In PackageSessionNodes there are 234 records, in PackageSessionNodesFinished there are 4256946 records.

Really would appreciate some help here as I'm stuck.

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about sql-server-2008