I would like to alternate the background color for rows in a table. Is there
a simple way to do this?
Basically I got it working by returning a rank column that identified the
row number. I then used the following code in the TableRow BackgroundColor
property:
IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
Seems like there must be an easier way then having to return a rank column
in the dataset associated with the table.Greg,
That's pretty much the only way I know to do it. Although, I used a
different formula then you did. Here's what I stuck in my Background Color
property box:
=iif(RowNumber(Nothing) mod 2=1, "OldLace", Nothing)
OldLace was the color I chose because I didn't want something hard on the
eyes. The Nothing leaves it as Transparent, which isn't really a color, but
doesn't force it to be another color. You could just as easily enter another
one of the available colors in the False part.
Does this help any?
Catadmin
"Greg Larsen" wrote:
> I would like to alternate the background color for rows in a table. Is there
> a simple way to do this?
> Basically I got it working by returning a rank column that identified the
> row number. I then used the following code in the TableRow BackgroundColor
> property:
> IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
> Seems like there must be an easier way then having to return a rank column
> in the dataset associated with the table.|||Yes this does help. By using RowNumber function you don't need to generate a
row number(or rank as I called it) as part of the dataset being returned.
Thank you that is exactly want I needed.
"Catadmin" wrote:
> Greg,
> That's pretty much the only way I know to do it. Although, I used a
> different formula then you did. Here's what I stuck in my Background Color
> property box:
> =iif(RowNumber(Nothing) mod 2=1, "OldLace", Nothing)
> OldLace was the color I chose because I didn't want something hard on the
> eyes. The Nothing leaves it as Transparent, which isn't really a color, but
> doesn't force it to be another color. You could just as easily enter another
> one of the available colors in the False part.
> Does this help any?
> Catadmin
> "Greg Larsen" wrote:
> > I would like to alternate the background color for rows in a table. Is there
> > a simple way to do this?
> >
> > Basically I got it working by returning a rank column that identified the
> > row number. I then used the following code in the TableRow BackgroundColor
> > property:
> >
> > IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
> >
> > Seems like there must be an easier way then having to return a rank column
> > in the dataset associated with the table.
Showing posts with label basically. Show all posts
Showing posts with label basically. Show all posts
Thursday, March 29, 2012
Saturday, February 25, 2012
ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts
Basically I am trying to apply an XML Schema to an XML column after data has been added to the table. I need to do this to generate a computed column for use in an index to improve the access times. While I was playing with the schema getting the format/syntax correct I needed to apply and remove the schema several times and got errors. The following is how the errors can easily be generated rather than how I encountered them initially.
Software=Windows 2003 Server, SQL Server 2005
The database table used, without the schema, was...
CREATE TABLE [dbo].[EventXML](
[EventID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[XMLData] [XML] NULL,
[msrepl_tran_version] [UNIQUEIDENTIFIER] NOT NULL DEFAULT (newid())
)
The data comprises…
85308 rows, EventID length=4, XMLData length=908…5576, Msrepl_tran_version length=16
Database restored from scratch.
Create schema collection for XmlData column…
CREATE XML SCHEMA COLLECTION dbo.EventXML_XMLData_SchemaCollection AS
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Event" >
<xsd:complexType>
<xsd:all>
<xsd:element name="categoryId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="formatStringId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeStrId" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeL" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeU" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="behaviour" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="severity" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="changeId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="idHolderId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="operatorId" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="doorId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="objectId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="oldStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="newStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="workstationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="logonName" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="identifierId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="readerId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="stationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="Alarm" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:all>
<xsd:element name="AlarmHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:choice>
<xsd:element name="AlarmHistoryItem" minOccurs="0" maxOccurs="unbounded" >
<xsd:complexType>
<xsd:attribute name="Action" type="xsd:string"/>
<xsd:attribute name="Time" type="xsd:string"/>
<xsd:attribute name="Operator" type="xsd:integer"/>
<xsd:attribute name="Workstation" type="xsd:integer"/>
<xsd:attribute name="PriorityThen" type="xsd:integer"/>
<xsd:attribute name="PriorityNow" type="xsd:integer"/>
<xsd:attribute name="StateThen" type="xsd:string"/>
<xsd:attribute name="StateNow" type="xsd:string"/>
<xsd:attribute name="ResolutionCode" type="xsd:integer"/>
<xsd:attribute name="Comment" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
<xsd:element name="ProcedureHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer"/>
<xsd:attribute name="Generated" type="xsd:string"/>
<xsd:attribute name="Reset" type="xsd:string"/>
<xsd:attribute name="State" type="xsd:integer"/>
<xsd:attribute name="Priority" type="xsd:integer"/>
<xsd:attribute name="AlarmTemplateID" type="xsd:integer"/>
<xsd:attribute name="AutoClear" type="xsd:string"/>
<xsd:attribute name="FormatStringID" type="xsd:integer"/>
<xsd:attribute name="ProcdeureTemplateID" type="xsd:integer"/>
<xsd:attribute name="Supervisor" type="xsd:string"/>
<xsd:attribute name="AlarmHandled" type="xsd:string"/>
<xsd:attribute name="ObjectId" type="xsd:integer"/>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer" use="required"/>
<xsd:attribute name="TypeName" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO
and then...
Modify XML column (XMLData) attributes...
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 62 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 71 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 81 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 106 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 78 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=Error in 293 seconds,
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 7th attempt and subsequent attempts.
Database restored from scratch and schema collection created, operations=
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 17 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 26 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 45 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 70 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 97 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 199 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 6th attempt and subsequent attempts.
Restore Database as before and create schema collection as before
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 67 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 82 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 150 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 236 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 5th attempt and subsequent attempts.
If you delete all the rows then the ALTER COLUMN completes without error.
A single row that exceeded 15000 bytes was added to the XML Data column of EventXML table without error.
All the errors that I could find that looked similar related either to SQL Server 2000 or SQL Server Mobile.
Hi Mark,
It seems like a bug in SQL2005. Do you mind to share your data with me so I can reproduce this problem at my side and fix it? You can contact me using this email jinghaol@.microsoft.com.
Thanks
Jinghao Liu
SQL Server Relational Engine XML team
|||Hi
FYI - I have encountered the same problem on one occasion with alter table. As it was just test data, I deleted the data and the alter then worked fine. I don't have a current repro
Just in case it helps, I also had the problem regularly when updating a column that was already typed against a collection
I created a separate filegroup and used "textimage on" to locate the XML data in the new filegroup
I have not had a problem with update since I used the new filegroup
|||Thanks for the reply. I am still interesting to know the real cause of the problem so we can fix it. You can contact me directly if you are able to repro the problem.
Thanks
Jinghao Liu - SQL Server Engine
|||Database information was sent to Jianghao Liu directly as requested. The following email was then received...
A bug has been filed for this problem. Thank you so much, Mark, for spend time to help us making SQL better!
Have a nice weekend!
Jinghao
|||
This reply has just been received from MSFT:
This bug has been resolved as “By Design”. SQL Server only allow altering a column certain amount of times. Alter column adds a column and removes a column. Once you've done this the space for the old column on the pages is not reclaimed.
If you are developing application base on continually ALTER COLUMN, then you need to redesign it.
Well thanks MSFT for that valuable input. Wouldn't it be nice to develop a large application where you know for definite what your schema is before you write a line of code like MSFT must do? This must be the "anti-Agile" methodology that MSFT use. For the rest of the world that don't get it exactly right first time, perhaps someone can explain that, given there are no verbs except ADD to alter a schema collection once it is applied to a column, how you are supposed to make changes without the use of ALTER COLUMN?
So this is "by design". I would love to have been at the design meeting where the requirement for "only allowing a column to be altered a certain number of times" was discussed. Do you think it was a "must have for release 1" or a "nice to have"?
And we wonder why people go anti-MSFT and turn to other platforms.
Anyway can't stop and chat, apparently I've got to go and redesign my app! .....I think I'll use Oracle!!
~swg
|||I agree with swg that this response is unacceptable.
I recognise that efforts should be made to minimise the occasions where an alter is needed - using up versions of the schemas if possible - which can be added without an alter. However, in the course of maintenance it seems almost unavoidable that alters will be needed
It appears that the only way currently available is to export the table contents into untyped XML so that you can drop the table and then recreate it with the column typed against the modified collection.
It looks like you have to assume that alter won't work if you are doing this sort of change in a production environment. I suggest that:
The minimum number of schemas needed are put into each collection - giving smaller collections|||
Hi!
Found a workaround. Good for solving other problems, too.
The mail problem with larga data fields is the fragmentation. The xml data column and the new nvarchar(max) should make the storage and indexes more optimal with the possibility of storing the value in-row or out-of-row.
Either way, after many updates your data pages can become fragmented, and the page usage can easily drop below 50% by using e.g xml columns (resultnig large data files).
The index pages can be optimized by rebuilding indexes.The data pages depend on the clustered indexes, so you should handle these types of issues by rebuilding the clustered index like any other indexes.
The same problem arises when modifying the xml schema on a column: the alter column makes the old column inactive but does not delete it from the occupied pages.
When rebuilding clustered index, the db engine reorders the data pages and the contained data, sorting out pages not needed any more: e.g the pages remained at last schema update.
Hope, I could help.
Bye:
Barrez
|||Thank you for your help
Regards
Mark Dooley
|||Hey thanks for this,
Brilliant piece of detective work.... stuff that MS should have offered actually. Presumably, if there isn't a clustered index on the table already, just adding one (and possibly dropping it again?) will achieve the same result?
I'll get Mark to mark your post as the Answer!
Cheers,
~swg
ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts
Basically I am trying to apply an XML Schema to an XML column after data has been added to the table. I need to do this to generate a computed column for use in an index to improve the access times. While I was playing with the schema getting the format/syntax correct I needed to apply and remove the schema several times and got errors. The following is how the errors can easily be generated rather than how I encountered them initially.
Software=Windows 2003 Server, SQL Server 2005
The database table used, without the schema, was...
CREATE TABLE [dbo].[EventXML](
[EventID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[XMLData] [XML] NULL,
[msrepl_tran_version] [UNIQUEIDENTIFIER] NOT NULL DEFAULT (newid())
)
The data comprises…
85308 rows, EventID length=4, XMLData length=908…5576, Msrepl_tran_version length=16
Database restored from scratch.
Create schema collection for XmlData column…
CREATE XML SCHEMA COLLECTION dbo.EventXML_XMLData_SchemaCollection AS
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Event" >
<xsd:complexType>
<xsd:all>
<xsd:element name="categoryId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="formatStringId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeStrId" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeL" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeU" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="behaviour" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="severity" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="changeId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="idHolderId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="operatorId" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="doorId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="objectId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="oldStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="newStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="workstationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="logonName" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="identifierId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="readerId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="stationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="Alarm" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:all>
<xsd:element name="AlarmHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:choice>
<xsd:element name="AlarmHistoryItem" minOccurs="0" maxOccurs="unbounded" >
<xsd:complexType>
<xsd:attribute name="Action" type="xsd:string"/>
<xsd:attribute name="Time" type="xsd:string"/>
<xsd:attribute name="Operator" type="xsd:integer"/>
<xsd:attribute name="Workstation" type="xsd:integer"/>
<xsd:attribute name="PriorityThen" type="xsd:integer"/>
<xsd:attribute name="PriorityNow" type="xsd:integer"/>
<xsd:attribute name="StateThen" type="xsd:string"/>
<xsd:attribute name="StateNow" type="xsd:string"/>
<xsd:attribute name="ResolutionCode" type="xsd:integer"/>
<xsd:attribute name="Comment" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
<xsd:element name="ProcedureHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer"/>
<xsd:attribute name="Generated" type="xsd:string"/>
<xsd:attribute name="Reset" type="xsd:string"/>
<xsd:attribute name="State" type="xsd:integer"/>
<xsd:attribute name="Priority" type="xsd:integer"/>
<xsd:attribute name="AlarmTemplateID" type="xsd:integer"/>
<xsd:attribute name="AutoClear" type="xsd:string"/>
<xsd:attribute name="FormatStringID" type="xsd:integer"/>
<xsd:attribute name="ProcdeureTemplateID" type="xsd:integer"/>
<xsd:attribute name="Supervisor" type="xsd:string"/>
<xsd:attribute name="AlarmHandled" type="xsd:string"/>
<xsd:attribute name="ObjectId" type="xsd:integer"/>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer" use="required"/>
<xsd:attribute name="TypeName" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO
and then...
Modify XML column (XMLData) attributes...
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 62 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 71 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 81 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 106 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 78 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=Error in 293 seconds,
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 7th attempt and subsequent attempts.
Database restored from scratch and schema collection created, operations=
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 17 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 26 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 45 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 70 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 97 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 199 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 6th attempt and subsequent attempts.
Restore Database as before and create schema collection as before
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 67 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 82 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 150 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 236 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 5th attempt and subsequent attempts.
If you delete all the rows then the ALTER COLUMN completes without error.
A single row that exceeded 15000 bytes was added to the XML Data column of EventXML table without error.
All the errors that I could find that looked similar related either to SQL Server 2000 or SQL Server Mobile.
Hi Mark,
It seems like a bug in SQL2005. Do you mind to share your data with me so I can reproduce this problem at my side and fix it? You can contact me using this email jinghaol@.microsoft.com.
Thanks
Jinghao Liu
SQL Server Relational Engine XML team
|||Hi
FYI - I have encountered the same problem on one occasion with alter table. As it was just test data, I deleted the data and the alter then worked fine. I don't have a current repro
Just in case it helps, I also had the problem regularly when updating a column that was already typed against a collection
I created a separate filegroup and used "textimage on" to locate the XML data in the new filegroup
I have not had a problem with update since I used the new filegroup
|||Thanks for the reply. I am still interesting to know the real cause of the problem so we can fix it. You can contact me directly if you are able to repro the problem.
Thanks
Jinghao Liu - SQL Server Engine
|||Database information was sent to Jianghao Liu directly as requested. The following email was then received...
A bug has been filed for this problem. Thank you so much, Mark, for spend time to help us making SQL better!
Have a nice weekend!
Jinghao
|||
This reply has just been received from MSFT:
This bug has been resolved as “By Design”. SQL Server only allow altering a column certain amount of times. Alter column adds a column and removes a column. Once you've done this the space for the old column on the pages is not reclaimed.
If you are developing application base on continually ALTER COLUMN, then you need to redesign it.
Well thanks MSFT for that valuable input. Wouldn't it be nice to develop a large application where you know for definite what your schema is before you write a line of code like MSFT must do? This must be the "anti-Agile" methodology that MSFT use. For the rest of the world that don't get it exactly right first time, perhaps someone can explain that, given there are no verbs except ADD to alter a schema collection once it is applied to a column, how you are supposed to make changes without the use of ALTER COLUMN?
So this is "by design". I would love to have been at the design meeting where the requirement for "only allowing a column to be altered a certain number of times" was discussed. Do you think it was a "must have for release 1" or a "nice to have"?
And we wonder why people go anti-MSFT and turn to other platforms.
Anyway can't stop and chat, apparently I've got to go and redesign my app! .....I think I'll use Oracle!!
~swg
|||I agree with swg that this response is unacceptable.
I recognise that efforts should be made to minimise the occasions where an alter is needed - using up versions of the schemas if possible - which can be added without an alter. However, in the course of maintenance it seems almost unavoidable that alters will be needed
It appears that the only way currently available is to export the table contents into untyped XML so that you can drop the table and then recreate it with the column typed against the modified collection.
It looks like you have to assume that alter won't work if you are doing this sort of change in a production environment. I suggest that:
The minimum number of schemas needed are put into each collection - giving smaller collections|||
Hi!
Found a workaround. Good for solving other problems, too.
The mail problem with larga data fields is the fragmentation. The xml data column and the new nvarchar(max) should make the storage and indexes more optimal with the possibility of storing the value in-row or out-of-row.
Either way, after many updates your data pages can become fragmented, and the page usage can easily drop below 50% by using e.g xml columns (resultnig large data files).
The index pages can be optimized by rebuilding indexes.The data pages depend on the clustered indexes, so you should handle these types of issues by rebuilding the clustered index like any other indexes.
The same problem arises when modifying the xml schema on a column: the alter column makes the old column inactive but does not delete it from the occupied pages.
When rebuilding clustered index, the db engine reorders the data pages and the contained data, sorting out pages not needed any more: e.g the pages remained at last schema update.
Hope, I could help.
Bye:
Barrez
|||Thank you for your help
Regards
Mark Dooley
|||Hey thanks for this,
Brilliant piece of detective work.... stuff that MS should have offered actually. Presumably, if there isn't a clustered index on the table already, just adding one (and possibly dropping it again?) will achieve the same result?
I'll get Mark to mark your post as the Answer!
Cheers,
~swg
ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts
Basically I am trying to apply an XML Schema to an XML column after data has been added to the table. I need to do this to generate a computed column for use in an index to improve the access times. While I was playing with the schema getting the format/syntax correct I needed to apply and remove the schema several times and got errors. The following is how the errors can easily be generated rather than how I encountered them initially.
Software=Windows 2003 Server, SQL Server 2005
The database table used, without the schema, was...
CREATE TABLE [dbo].[EventXML](
[EventID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[XMLData] [XML] NULL,
[msrepl_tran_version] [UNIQUEIDENTIFIER] NOT NULL DEFAULT (newid())
)
The data comprises…
85308 rows, EventID length=4, XMLData length=908…5576, Msrepl_tran_version length=16
Database restored from scratch.
Create schema collection for XmlData column…
CREATE XML SCHEMA COLLECTION dbo.EventXML_XMLData_SchemaCollection AS
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Event" >
<xsd:complexType>
<xsd:all>
<xsd:element name="categoryId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="formatStringId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeStrId" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeL" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeU" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="behaviour" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="severity" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="changeId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="idHolderId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="operatorId" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="doorId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="objectId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="oldStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="newStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="workstationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="logonName" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="identifierId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="readerId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="stationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="Alarm" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:all>
<xsd:element name="AlarmHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:choice>
<xsd:element name="AlarmHistoryItem" minOccurs="0" maxOccurs="unbounded" >
<xsd:complexType>
<xsd:attribute name="Action" type="xsd:string"/>
<xsd:attribute name="Time" type="xsd:string"/>
<xsd:attribute name="Operator" type="xsd:integer"/>
<xsd:attribute name="Workstation" type="xsd:integer"/>
<xsd:attribute name="PriorityThen" type="xsd:integer"/>
<xsd:attribute name="PriorityNow" type="xsd:integer"/>
<xsd:attribute name="StateThen" type="xsd:string"/>
<xsd:attribute name="StateNow" type="xsd:string"/>
<xsd:attribute name="ResolutionCode" type="xsd:integer"/>
<xsd:attribute name="Comment" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
<xsd:element name="ProcedureHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer"/>
<xsd:attribute name="Generated" type="xsd:string"/>
<xsd:attribute name="Reset" type="xsd:string"/>
<xsd:attribute name="State" type="xsd:integer"/>
<xsd:attribute name="Priority" type="xsd:integer"/>
<xsd:attribute name="AlarmTemplateID" type="xsd:integer"/>
<xsd:attribute name="AutoClear" type="xsd:string"/>
<xsd:attribute name="FormatStringID" type="xsd:integer"/>
<xsd:attribute name="ProcdeureTemplateID" type="xsd:integer"/>
<xsd:attribute name="Supervisor" type="xsd:string"/>
<xsd:attribute name="AlarmHandled" type="xsd:string"/>
<xsd:attribute name="ObjectId" type="xsd:integer"/>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer" use="required"/>
<xsd:attribute name="TypeName" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO
and then...
Modify XML column (XMLData) attributes...
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 62 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 71 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 81 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 106 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 78 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=Error in 293 seconds,
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 7th attempt and subsequent attempts.
Database restored from scratch and schema collection created, operations=
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 17 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 26 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 45 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 70 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 97 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 199 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 6th attempt and subsequent attempts.
Restore Database as before and create schema collection as before
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 67 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 82 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 150 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 236 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 5th attempt and subsequent attempts.
If you delete all the rows then the ALTER COLUMN completes without error.
A single row that exceeded 15000 bytes was added to the XML Data column of EventXML table without error.
All the errors that I could find that looked similar related either to SQL Server 2000 or SQL Server Mobile.
Hi Mark,
It seems like a bug in SQL2005. Do you mind to share your data with me so I can reproduce this problem at my side and fix it? You can contact me using this email jinghaol@.microsoft.com.
Thanks
Jinghao Liu
SQL Server Relational Engine XML team
|||Hi
FYI - I have encountered the same problem on one occasion with alter table. As it was just test data, I deleted the data and the alter then worked fine. I don't have a current repro
Just in case it helps, I also had the problem regularly when updating a column that was already typed against a collection
I created a separate filegroup and used "textimage on" to locate the XML data in the new filegroup
I have not had a problem with update since I used the new filegroup
|||Thanks for the reply. I am still interesting to know the real cause of the problem so we can fix it. You can contact me directly if you are able to repro the problem.
Thanks
Jinghao Liu - SQL Server Engine
|||Database information was sent to Jianghao Liu directly as requested. The following email was then received...
A bug has been filed for this problem. Thank you so much, Mark, for spend time to help us making SQL better!
Have a nice weekend!
Jinghao
|||
This reply has just been received from MSFT:
This bug has been resolved as “By Design”. SQL Server only allow altering a column certain amount of times. Alter column adds a column and removes a column. Once you've done this the space for the old column on the pages is not reclaimed.
If you are developing application base on continually ALTER COLUMN, then you need to redesign it.
Well thanks MSFT for that valuable input. Wouldn't it be nice to develop a large application where you know for definite what your schema is before you write a line of code like MSFT must do? This must be the "anti-Agile" methodology that MSFT use. For the rest of the world that don't get it exactly right first time, perhaps someone can explain that, given there are no verbs except ADD to alter a schema collection once it is applied to a column, how you are supposed to make changes without the use of ALTER COLUMN?
So this is "by design". I would love to have been at the design meeting where the requirement for "only allowing a column to be altered a certain number of times" was discussed. Do you think it was a "must have for release 1" or a "nice to have"?
And we wonder why people go anti-MSFT and turn to other platforms.
Anyway can't stop and chat, apparently I've got to go and redesign my app! .....I think I'll use Oracle!!
~swg
|||I agree with swg that this response is unacceptable.
I recognise that efforts should be made to minimise the occasions where an alter is needed - using up versions of the schemas if possible - which can be added without an alter. However, in the course of maintenance it seems almost unavoidable that alters will be needed
It appears that the only way currently available is to export the table contents into untyped XML so that you can drop the table and then recreate it with the column typed against the modified collection.
It looks like you have to assume that alter won't work if you are doing this sort of change in a production environment. I suggest that:
The minimum number of schemas needed are put into each collection - giving smaller collections|||Hi!
Found a workaround. Good for solving other problems, too.
The mail problem with larga data fields is the fragmentation. The xml data column and the new nvarchar(max) should make the storage and indexes more optimal with the possibility of storing the value in-row or out-of-row.
Either way, after many updates your data pages can become fragmented, and the page usage can easily drop below 50% by using e.g xml columns (resultnig large data files).
The index pages can be optimized by rebuilding indexes.The data pages depend on the clustered indexes, so you should handle these types of issues by rebuilding the clustered index like any other indexes.
The same problem arises when modifying the xml schema on a column: the alter column makes the old column inactive but does not delete it from the occupied pages.
When rebuilding clustered index, the db engine reorders the data pages and the contained data, sorting out pages not needed any more: e.g the pages remained at last schema update.
Hope, I could help.
Bye:
Barrez
|||Thank you for your help
Regards
Mark Dooley
|||Hey thanks for this,
Brilliant piece of detective work.... stuff that MS should have offered actually. Presumably, if there isn't a clustered index on the table already, just adding one (and possibly dropping it again?) will achieve the same result?
I'll get Mark to mark your post as the Answer!
Cheers,
~swg
ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts
Basically I am trying to apply an XML Schema to an XML column after data has been added to the table. I need to do this to generate a computed column for use in an index to improve the access times. While I was playing with the schema getting the format/syntax correct I needed to apply and remove the schema several times and got errors. The following is how the errors can easily be generated rather than how I encountered them initially.
Software=Windows 2003 Server, SQL Server 2005
The database table used, without the schema, was...
CREATE TABLE [dbo].[EventXML](
[EventID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[XMLData] [XML] NULL,
[msrepl_tran_version] [UNIQUEIDENTIFIER] NOT NULL DEFAULT (newid())
)
The data comprises…
85308 rows, EventID length=4, XMLData length=908…5576, Msrepl_tran_version length=16
Database restored from scratch.
Create schema collection for XmlData column…
CREATE XML SCHEMA COLLECTION dbo.EventXML_XMLData_SchemaCollection AS
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Event" >
<xsd:complexType>
<xsd:all>
<xsd:element name="categoryId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="formatStringId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeStrId" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeL" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeU" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="behaviour" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="severity" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="changeId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="idHolderId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="operatorId" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="doorId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="objectId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="oldStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="newStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="workstationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="logonName" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="identifierId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="readerId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="stationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="Alarm" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:all>
<xsd:element name="AlarmHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:choice>
<xsd:element name="AlarmHistoryItem" minOccurs="0" maxOccurs="unbounded" >
<xsd:complexType>
<xsd:attribute name="Action" type="xsd:string"/>
<xsd:attribute name="Time" type="xsd:string"/>
<xsd:attribute name="Operator" type="xsd:integer"/>
<xsd:attribute name="Workstation" type="xsd:integer"/>
<xsd:attribute name="PriorityThen" type="xsd:integer"/>
<xsd:attribute name="PriorityNow" type="xsd:integer"/>
<xsd:attribute name="StateThen" type="xsd:string"/>
<xsd:attribute name="StateNow" type="xsd:string"/>
<xsd:attribute name="ResolutionCode" type="xsd:integer"/>
<xsd:attribute name="Comment" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
<xsd:element name="ProcedureHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer"/>
<xsd:attribute name="Generated" type="xsd:string"/>
<xsd:attribute name="Reset" type="xsd:string"/>
<xsd:attribute name="State" type="xsd:integer"/>
<xsd:attribute name="Priority" type="xsd:integer"/>
<xsd:attribute name="AlarmTemplateID" type="xsd:integer"/>
<xsd:attribute name="AutoClear" type="xsd:string"/>
<xsd:attribute name="FormatStringID" type="xsd:integer"/>
<xsd:attribute name="ProcdeureTemplateID" type="xsd:integer"/>
<xsd:attribute name="Supervisor" type="xsd:string"/>
<xsd:attribute name="AlarmHandled" type="xsd:string"/>
<xsd:attribute name="ObjectId" type="xsd:integer"/>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer" use="required"/>
<xsd:attribute name="TypeName" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO
and then...
Modify XML column (XMLData) attributes...
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 62 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 71 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 81 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 106 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 78 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=Error in 293 seconds,
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 7th attempt and subsequent attempts.
Database restored from scratch and schema collection created, operations=
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 17 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 26 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 45 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 70 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 97 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 199 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 6th attempt and subsequent attempts.
Restore Database as before and create schema collection as before
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 67 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 82 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 150 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 236 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 5th attempt and subsequent attempts.
If you delete all the rows then the ALTER COLUMN completes without error.
A single row that exceeded 15000 bytes was added to the XML Data column of EventXML table without error.
All the errors that I could find that looked similar related either to SQL Server 2000 or SQL Server Mobile.
Hi Mark,
It seems like a bug in SQL2005. Do you mind to share your data with me so I can reproduce this problem at my side and fix it? You can contact me using this email jinghaol@.microsoft.com.
Thanks
Jinghao Liu
SQL Server Relational Engine XML team
|||Hi
FYI - I have encountered the same problem on one occasion with alter table. As it was just test data, I deleted the data and the alter then worked fine. I don't have a current repro
Just in case it helps, I also had the problem regularly when updating a column that was already typed against a collection
I created a separate filegroup and used "textimage on" to locate the XML data in the new filegroup
I have not had a problem with update since I used the new filegroup
|||Thanks for the reply. I am still interesting to know the real cause of the problem so we can fix it. You can contact me directly if you are able to repro the problem.
Thanks
Jinghao Liu - SQL Server Engine
|||Database information was sent to Jianghao Liu directly as requested. The following email was then received...
A bug has been filed for this problem. Thank you so much, Mark, for spend time to help us making SQL better!
Have a nice weekend!
Jinghao
|||
This reply has just been received from MSFT:
This bug has been resolved as “By Design”. SQL Server only allow altering a column certain amount of times. Alter column adds a column and removes a column. Once you've done this the space for the old column on the pages is not reclaimed.
If you are developing application base on continually ALTER COLUMN, then you need to redesign it.
Well thanks MSFT for that valuable input. Wouldn't it be nice to develop a large application where you know for definite what your schema is before you write a line of code like MSFT must do? This must be the "anti-Agile" methodology that MSFT use. For the rest of the world that don't get it exactly right first time, perhaps someone can explain that, given there are no verbs except ADD to alter a schema collection once it is applied to a column, how you are supposed to make changes without the use of ALTER COLUMN?
So this is "by design". I would love to have been at the design meeting where the requirement for "only allowing a column to be altered a certain number of times" was discussed. Do you think it was a "must have for release 1" or a "nice to have"?
And we wonder why people go anti-MSFT and turn to other platforms.
Anyway can't stop and chat, apparently I've got to go and redesign my app! .....I think I'll use Oracle!!
~swg
|||I agree with swg that this response is unacceptable.
I recognise that efforts should be made to minimise the occasions where an alter is needed - using up versions of the schemas if possible - which can be added without an alter. However, in the course of maintenance it seems almost unavoidable that alters will be needed
It appears that the only way currently available is to export the table contents into untyped XML so that you can drop the table and then recreate it with the column typed against the modified collection.
It looks like you have to assume that alter won't work if you are doing this sort of change in a production environment. I suggest that:
The minimum number of schemas needed are put into each collection - giving smaller collections|||
Hi!
Found a workaround. Good for solving other problems, too.
The mail problem with larga data fields is the fragmentation. The xml data column and the new nvarchar(max) should make the storage and indexes more optimal with the possibility of storing the value in-row or out-of-row.
Either way, after many updates your data pages can become fragmented, and the page usage can easily drop below 50% by using e.g xml columns (resultnig large data files).
The index pages can be optimized by rebuilding indexes.The data pages depend on the clustered indexes, so you should handle these types of issues by rebuilding the clustered index like any other indexes.
The same problem arises when modifying the xml schema on a column: the alter column makes the old column inactive but does not delete it from the occupied pages.
When rebuilding clustered index, the db engine reorders the data pages and the contained data, sorting out pages not needed any more: e.g the pages remained at last schema update.
Hope, I could help.
Bye:
Barrez
|||Thank you for your help
Regards
Mark Dooley
|||Hey thanks for this,
Brilliant piece of detective work.... stuff that MS should have offered actually. Presumably, if there isn't a clustered index on the table already, just adding one (and possibly dropping it again?) will achieve the same result?
I'll get Mark to mark your post as the Answer!
Cheers,
~swg
Sunday, February 19, 2012
Allowing ReadOnly rights to a database
Hi,
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only access
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete records.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>
|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:
> Hi,
> I am currently having problems assigning read only privileges to a database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to access
> a database on SQL server 2000. I only want this user to have read only access
> to this database. I have added the user into the Logins in SQL server (i.e.
> domain\username) and granted them the db_datareader role to that database. I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only access
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete records.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>
|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:
> Hi,
> I am currently having problems assigning read only privileges to a database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to access
> a database on SQL server 2000. I only want this user to have read only access
> to this database. I have added the user into the Logins in SQL server (i.e.
> domain\username) and granted them the db_datareader role to that database. I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>
Allowing ReadOnly rights to a database
Hi,
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only access
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete records.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>|||Hello Jen,
The person in question is probably already in, but using a
role / group such as BUILTIN\ADMINISTRATORS.
Thats the reason why they can still do the delete, insert
and update stuff as well as the select.
Look under the security, logons and see which types are
Windows Groups, then have a chat to your Server Bods to
see if the user is already included in the group.
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain
>--Original Message--
>Hi,
>I am currently having problems assigning read only
privileges to a database
>for a specific user.
>Basically, I have an application which uses Windows
Authentication to access
>a database on SQL server 2000. I only want this user to
have read only access
>to this database. I have added the user into the Logins
in SQL server (i.e.
>domain\username) and granted them the db_datareader role
to that database. I
>was assuming that this would only allow them to have read
access to the
>database, but this is not the case as they can add,
modify and delete records.
>Any help/advice would be appreciated as this is driving
me mad.
>Thanks,
>Jen
>.
>|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
--
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:
> Hi,
> I am currently having problems assigning read only privileges to a database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to access
> a database on SQL server 2000. I only want this user to have read only access
> to this database. I have added the user into the Logins in SQL server (i.e.
> domain\username) and granted them the db_datareader role to that database. I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only access
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete records.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>|||Hello Jen,
The person in question is probably already in, but using a
role / group such as BUILTIN\ADMINISTRATORS.
Thats the reason why they can still do the delete, insert
and update stuff as well as the select.
Look under the security, logons and see which types are
Windows Groups, then have a chat to your Server Bods to
see if the user is already included in the group.
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain
>--Original Message--
>Hi,
>I am currently having problems assigning read only
privileges to a database
>for a specific user.
>Basically, I have an application which uses Windows
Authentication to access
>a database on SQL server 2000. I only want this user to
have read only access
>to this database. I have added the user into the Logins
in SQL server (i.e.
>domain\username) and granted them the db_datareader role
to that database. I
>was assuming that this would only allow them to have read
access to the
>database, but this is not the case as they can add,
modify and delete records.
>Any help/advice would be appreciated as this is driving
me mad.
>Thanks,
>Jen
>.
>|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
--
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:
> Hi,
> I am currently having problems assigning read only privileges to a database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to access
> a database on SQL server 2000. I only want this user to have read only access
> to this database. I have added the user into the Logins in SQL server (i.e.
> domain\username) and granted them the db_datareader role to that database. I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>
Allowing ReadOnly rights to a database
Hi,
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only acces
s
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete record
s.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:
> Hi,
> I am currently having problems assigning read only privileges to a databas
e
> for a specific user.
> Basically, I have an application which uses Windows Authentication to acce
ss
> a database on SQL server 2000. I only want this user to have read only acc
ess
> to this database. I have added the user into the Logins in SQL server (i.e
.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete reco
rds.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only acces
s
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete record
s.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:
> Hi,
> I am currently having problems assigning read only privileges to a databas
e
> for a specific user.
> Basically, I have an application which uses Windows Authentication to acce
ss
> a database on SQL server 2000. I only want this user to have read only acc
ess
> to this database. I have added the user into the Logins in SQL server (i.e
.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete reco
rds.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>
Thursday, February 9, 2012
All Hardware Being Equal
Does SQL Server 2005 Express perform equal to SQL Server 2005 Enterprise? Basically, if the hardware that both editions were deployed on was identical and configured with in conjunction with the hardware limitations that Express imposes, would the two editions perform the same when running the same querries?
I have read that SQL Express uses the same database engine but does this mean that the Express edition will perform as well as the Enterprise edition?
SQL Express has certain limits. Only 1 CPU is used, only 1GB of RAM is used, no 64-bit support, databases can't be larger than 4GB. If you're comparing SQL Express to SQL Enterprise on a hardware above these things, Enterprise will definitely be faster as it doesn't have such limits.
From a software standpoint, the only performance feature difference I'm aware of is that SQL Enterprise will consider Indexed Views when optimizing queries... any lesser version (inlcuding SQL Standard) will not do this.
There may be differences in the default configuration of the editions, too.
So, as you can see, it's a bit tricky to get an apples-to-apples comparison :-) All things considered, though, the performance should be pretty close on small databases.
-Ryan / Kardax
Subscribe to:
Posts (Atom)