libranoelrose
New Member
In our database, there is a table with a column of \[code\]XML\[/code\] datatype. The column was mandatory (\[code\]not null\[/code\]), I needed to make it non-mandatory (\[code\]null\[/code\]). I wrote a script similar to:\[code\]alter table [SomeSchema].[SomeTable] alter column [SomeColumn] XML null\[/code\]I needed to write/test a rollback script, so I wrote one similar to:\[code\]alter table [SomeSchema].[SomeTable] alter column [SomeColumn] XML not null\[/code\]The initial statement executed correctly. When I attempted to test/execute the rollback, I got the following error:\[quote\] Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8082 which is greater than the allowable maximum row size of 8060. The statement has been terminated.\[/quote\]The issue seems to relate to row overflow, which supposedly shouldn't apply to XML types (but seems to in this circumstance). This issue only occurs when certain records are in the table (i.e. it's affected by data), but it doesn't seem to directly correlate to the size of the data. For example, one record had an XML length of 10,473 characters and triggered this issue; another record had an XML length of 159,072 characters and didn't trigger the issue.Setup:
Cannot create a row of size 8082 which is greater than the allowable maximum row size of 8060. The statement has been terminated.\[/quote\]The issue seems to relate to row overflow, which supposedly shouldn't apply to XML types (but seems to in this circumstance). This issue only occurs when certain records are in the table (i.e. it's affected by data), but it doesn't seem to directly correlate to the size of the data. For example, one record had an XML length of 10,473 characters and triggered this issue; another record had an XML length of 159,072 characters and didn't trigger the issue.Setup:
- Windows XP SP3
- SQL Server 2008 R2