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.
