Alternate to a not in query

-- tested schema below --
-- create tables --
create table tbl_test
(serialnumber char(12))
go
create table tbl_test2
(serialnumber char(12),
exportedflag int)
go
--insert data --
insert into tbl_test2 values ('123456789010',0)
insert into tbl_test2 values ('123456789011',0)
insert into tbl_test2 values ('123456789012',0)
insert into tbl_test2 values ('123456789013',0)
insert into tbl_test2 values ('123456789014',0)
insert into tbl_test2 values ('123456789015',0)
insert into tbl_test2 values ('123456789016',0)
insert into tbl_test2 values ('123456789017',0)
insert into tbl_test2 values ('123456789018',0)
insert into tbl_test2 values ('123456789019',0)

insert into tbl_test values ('123456789011')
insert into tbl_test values ('123456789012')
insert into tbl_test values ('123456789013')
insert into tbl_test values ('123456789014')
insert into tbl_test values ('123456789015')

-- query --
Select serialnumber from tbl_test2
where serialnumber
not in (select serialnumber from tbl_test) and
exportedflag=0

This query runs quite fast with only the data above but when both
tables get million plus rows, the query simply bogs down. Is there a
better way to write this query?Select serialnumber
from tbl_test2 a
left joint tbl_test b on a. serialnumber = b.serialnumber
where (b.serialnumber IS NULL)
AND (a.exportedflag=0)|||There is another way to write the query, but it's not better (in fact,
I think it's worse):

Select tbl_test2.serialnumber from tbl_test2
left join tbl_test on tbl_test2.serialnumber=tbl_test.serialnumber
where exportedflag=0 and tbl_test.serialnumber is null

To improve the performance of this query, you should create primary
keys on the tables. Besides the conceptual benefits of a proper design,
this would accomplish (at least) the following things:
- create an index on the serialnumber column
- declare that the serialnumber column does not allow duplicates
- declare that the serialnumber column does not allow nulls
These things will help the Query Optimizer very much to create a better
execution plan.

Razvan|||
Razvan Socol wrote:
> There is another way to write the query, but it's not better (in fact,
> I think it's worse):
> Select tbl_test2.serialnumber from tbl_test2
> left join tbl_test on tbl_test2.serialnumber=tbl_test.serialnumber
> where exportedflag=0 and tbl_test.serialnumber is null

Razvan,

Why worse?

The common wisdom seems to be that it is always more efficient
eliminate nested subqueries, if possible.

My understanding is that the optimizer will internally eliminate the
subquery by doing a left join as above if it can.|||Ira Gladnick (IraGladnick@.yahoo.com) writes:
> Why worse?
> The common wisdom seems to be that it is always more efficient
> eliminate nested subqueries, if possible.

It's worse, becase it does not express the intent of the query equally
well, and therefore can contribute to higher maintenance costs.

> My understanding is that the optimizer will internally eliminate the
> subquery by doing a left join as above if it can.

I don't know if this is the case, but in such case there is even less
reason to rewrite the query in an obscure way.

I would write the query as:

Select serialnumber
from tbl_test2 t2
where not exists (select *
from tbl_test t
where t2.serialnuber = t.serialnumber)
and exportedflag=0

In SQL 6.5 this would typically perform better than NOT IN. But I believe
SQL 2000 will rewrite NOT IN to NOT EXISTS internally, so it is not that
much of an issue for performance. But NOT EXISTS is more general to use
than NOT IN, because you can handle multi-column conditions. Furthermore,
if there are NULL values involved, NOT IN can give you surpriese.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(kjaggi@.hotmail.com) writes:
> -- query --
> Select serialnumber from tbl_test2
> where serialnumber
> not in (select serialnumber from tbl_test) and
> exportedflag=0
> This query runs quite fast with only the data above but when both
> tables get million plus rows, the query simply bogs down. Is there a
> better way to write this query?

Beside the obvious point from Razvan about indexes, if you are on a multi-
CPU box, you can try this at the end of the query:

OPTION (MAXDOP 1)

this turns off parallelism. I've seen SQL Server use massive parallel
plans for this type of query, when a non-parallel plan have been much
faster.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Alternate Synchronization Server Schema Change Problem

Greetings:
We have set up a test scenario in Sql Server 2000 using merge replication
with:
A primary synchronization server (Publisher A)
An alternate synchronization partner (Publisher B)
Two subscribers to both publishers (Subscriber Y and Subscriber Z)
After some initial difficulties, we were able to get the alternate synch
partner running where both subscribers will successfully synch to either
machine without difficulty. (See thread “Alternate Synchronization Partner
-- Error”, originally posted on March 10.)
The next part of testing phase before going live is to test how schema
changes occur with alternate synch partners. We find NO documentation on
this subject anywhere, so if anyone has resources on how to do schema changes
where an alternate synchronization partner is involved, we would be very,
very grateful.
Our basic question: How are schema changes done where an alternate
synchronization partner is involved? We want to know exact procedures to do
the following:
-- Add a column
-- Drop a column
-- Add a table
We have tried to add a column as follows:
1.Used “sp_repladdcolumn” and added a column to one table in Publisher A,
the primary publisher. (See next paragraph on warning received.)
2.Synchronized the Publisher B, the alternate synch partner and it brought
in the column.
3.Synchronized Subscriber Y to Publisher A, and it brought in the column to
the subscriber.
At #1, when “sp_repladdcolumn” ran, I received the following warning:
“Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp_sel_D536588A75244907040F5997233E41C8_pal'.
The stored procedure will still be created.”
The Microsoft knowledge base article 811483 says this warning can be
ignored, but I wonder about being able to ignore this warning because of the
results we received described below.
Results from above process:
-- NO DATA UPDATES FROM PUBLISHER A WILL PROPAGATE TO EITHER PUBLISHER B,
THE ALTERNATE, OR TO SUBSCRIBER Y WHEN SYNCRHONIZING. New records added at
Publisher A do propagate, but no updates of existing data will. Inserts
propagate – updates do not propagate.
-- In addition, all data changes, inserts, and deletes made at either
Publisher B, the alternate synch partner, or at Subscriber Y, DO propagate
back to the primary Publisher A.
When I found that no data updates would propagate to the alternate synch
partner or the subscribers, I then
1.Used “sp_repldropcolumn” to drop the column I had added on Publisher A,
the primary.
2.Synchronized Publisher B, the alternate synch partner and it dropped the
column.
3.Synchronized Subscriber Y and it dropped the column.
I then tested data updates from Publisher A, the primary, and the updates
all began to propagate to Publisher B, the alternate and Subscriber Y once
again.
Summary:
-- A column was added to the Publisher A and the schema change propagated
to Publisher B and Subscriber Y.
-- After the schema change was propagated, any data UPDATES on Publisher A
would not propagate to the Publisher B or Subscriber Y. INSERTS however,
would propagate.
-- The column was dropped at Publisher A and that schema change propagated
to Publisher B and Subscriber Y.
-- Data updates from Publish A began to propagate again go Publisher B and
Subscriber Y.
What is wrong with the procedures I am using to make schema changes that are
causing the primary synchronization server to stop propagating data updates
after the column is added, but when the column is dropped, updates begin to
propagate again?
Any procedures for schema changes when an alternate synchronization partner
is involved would be very much appreciated.
Thank you.
Bill
Hi Bill,
From your descriptions, I understood that you would like to why
sp_repladdcolumn does not take effect. If I have misunderstood your
concern, please feel free to point it out.
First of all, please understand that replication issues tend to be very
complex and hard to troubleshoot in newsgroups. If you need detail and
prompt assistance, I recommend that you open a Support incident with
Microsoft Customer Service and Support (CSS) so that a dedicated Support
Professional can work with you in a more timely and efficient manner. If
you need any help in this regard, please let me know.
For a complete list of Microsoft Customer Service and Support phone
numbers, please go to the following address on the World Wide Web:
<http://support.microsoft.com/directory/overview.asp>
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Secondly, based on my knowledge, once the column is added at the original
publisher, the merge agent between the original publisher and the alternate
publisher has to be run so that the new schema is sent to the alternate
publisher. Also, the merge agent between the original publisher and the
subscriber needs to be run so that the schema definition is sent here. As
long as the schema of the replicating pairs match, there are no issues.
It seems your process is OK and it's weird that you are not able to
synchronize the data. Do you find any other error messages in Event Logs or
SQL Logs?
You may generate the file as following Knowledge Base article asked and
then send to me direcly v-mingqc@.online.microsoft.com (plase ensure to
remove 'online' in the email address as it's only for SPAM)
HOW TO: Enable Replication Agents for Logging to Output Files in SQL Server
http://support.microsoft.com/?id=312292
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Michael:
Thanks for the information. I was preparing to send you the logs when I
suddenly wondered if I needed to reinitialize the pull subscriptions. So I
decided to try that first, and it worked. After reinitializing all
subscriptions, all data changes began to synchronize with no errors or
problems.
I then ran several additional tests with adding and dropping new columns.
When I reinitialized the subscriptions, everything synchronized. When I did
not reinitialize, the schema would propagate, but data changes would not.
This is clearly different from when there is no alternate synch server. We
have added and deleted columns many times before with sp_repladdcolumn or
sp_repldropcolumn when no alternate synch server was involved. Data changes
always continued to synchronize properly in that circumstance. Perhaps some
note needs to be put in BOL about this difference when an alternate
synchronization server is involved? Just a suggestion that might save others
time.
The next test I am going to try is to add a new table (article) to our test
database and see if I can get it to propagate to the alternate synch server
and the two subscribers we have.
You have been a great help in pointing me the right direction. I really
appreciate it.
Bill
"Michael Cheng [MSFT]" wrote:

> Hi Bill,
> From your descriptions, I understood that you would like to why
> sp_repladdcolumn does not take effect. If I have misunderstood your
> concern, please feel free to point it out.
> First of all, please understand that replication issues tend to be very
> complex and hard to troubleshoot in newsgroups. If you need detail and
> prompt assistance, I recommend that you open a Support incident with
> Microsoft Customer Service and Support (CSS) so that a dedicated Support
> Professional can work with you in a more timely and efficient manner. If
> you need any help in this regard, please let me know.
> For a complete list of Microsoft Customer Service and Support phone
> numbers, please go to the following address on the World Wide Web:
> <http://support.microsoft.com/directory/overview.asp>
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> Secondly, based on my knowledge, once the column is added at the original
> publisher, the merge agent between the original publisher and the alternate
> publisher has to be run so that the new schema is sent to the alternate
> publisher. Also, the merge agent between the original publisher and the
> subscriber needs to be run so that the schema definition is sent here. As
> long as the schema of the replicating pairs match, there are no issues.
> It seems your process is OK and it's weird that you are not able to
> synchronize the data. Do you find any other error messages in Event Logs or
> SQL Logs?
> You may generate the file as following Knowledge Base article asked and
> then send to me direcly v-mingqc@.online.microsoft.com (plase ensure to
> remove 'online' in the email address as it's only for SPAM)
> HOW TO: Enable Replication Agents for Logging to Output Files in SQL Server
> http://support.microsoft.com/?id=312292
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
I searched internal but limited resource on this topic about why
reinitializing make all things go smoothly as I cannot reproduce it on my
side as you described. If you want to fingure out the root cause of this
issue, you'd better send the logs to our PSS guys as Replication issues
might turn out to be very complicated.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael;
With the reinitialization of the subscriptions, the schema changes and data
changes synchronized perfectly on numerous tests. I put together a process
for us to use in these circumstances because it is a bit different between
add/drop a column and adding a new table. In any case, I did get them work
fine with the altternate synch server.
If I did not reinitialize the subscriptions, the schema would propagate, but
the data changes would not from the primary server. I tried this in several
tests and could not get it go, but since I have a procedure that works now,
my business partners and I are happy with it and will go live in the next
month or two with our alternate synchronization partner.
I appreciate all your help and suggestions.
Bill
"Michael Cheng [MSFT]" wrote:

> Hi Bill,
> I searched internal but limited resource on this topic about why
> reinitializing make all things go smoothly as I cannot reproduce it on my
> side as you described. If you want to fingure out the root cause of this
> issue, you'd better send the logs to our PSS guys as Replication issues
> might turn out to be very complicated.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
Thanks for your perfect summary and I believe others will also benefit from
your great work
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Altering XML Schema Collection Problem

Hi,
I'm trying to add an extra element into a schema collection and keep getting
an error message:
"Msg 9455, Level 16, State 1, Line 1
XML parsing: line 3, character 1, illegal qualified name character
"
The SQL I'm using is:
alter xml schema collection dbo.EmployeeSchemaCollection ADD N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
<xsd:element name="Employee">
<xsd:complexType>
<xsd:element name="HireDate" type="xsd:datetime" />
</xsd:complexType>
</xsd:element>
</xsd:schema>'
Can anyone help?
ThanksI don't know this stuff much however should not be a ">" in the end of the
second line? (<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema")
--
Ekrem Ã?nsoy
"John S" <js162@.newsgroup.nospam> wrote in message
news:F7FE9209-3176-4742-97AD-9689A20085A1@.microsoft.com...
> Hi,
> I'm trying to add an extra element into a schema collection and keep
> getting
> an error message:
> "Msg 9455, Level 16, State 1, Line 1
> XML parsing: line 3, character 1, illegal qualified name character
> "
> The SQL I'm using is:
> alter xml schema collection dbo.EmployeeSchemaCollection ADD N'
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> <xsd:element name="Employee">
> <xsd:complexType>
> <xsd:element name="HireDate" type="xsd:datetime" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>'
> Can anyone help?
> Thanks|||It's so easy to miss the obvious!
Thanks
JS
"Ekrem Ã?nsoy" wrote:
> I don't know this stuff much however should not be a ">" in the end of the
> second line? (<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema")
> --
> Ekrem Ã?nsoy
>
> "John S" <js162@.newsgroup.nospam> wrote in message
> news:F7FE9209-3176-4742-97AD-9689A20085A1@.microsoft.com...
> > Hi,
> >
> > I'm trying to add an extra element into a schema collection and keep
> > getting
> > an error message:
> > "Msg 9455, Level 16, State 1, Line 1
> > XML parsing: line 3, character 1, illegal qualified name character
> > "
> >
> > The SQL I'm using is:
> >
> > alter xml schema collection dbo.EmployeeSchemaCollection ADD N'
> > <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> > <xsd:element name="Employee">
> > <xsd:complexType>
> > <xsd:element name="HireDate" type="xsd:datetime" />
> > </xsd:complexType>
> > </xsd:element>
> > </xsd:schema>'
> >
> > Can anyone help?
> >
> > Thanks
>sql

Altering stored procedure which is part of sys schema

I am trying to alter sys.sp_helpmergeconflictrows which is part fof sys schema and is in System Stored Procedures.

Reason why I need this is because Conglict Viewer in merge replication fails to show data from one of my tables, because aforementioned sp fails during execution. It fails because sql query is declared as nvarchar(4000) and it needs to be longer. So, I tired to change it to nvarchar(max), but I cannot.

I tried few things in order to gain permission to alter that sp, but I fial always.

Can it be done at all, and if can, how?

Thanks

If you have a bug in the conflict viewer (or more specifically this stored proc), you should open a ticket with MS Tech Support to have this resolved.

Bryan

sql

Altering multiple objects schema

Hi,

I need to change the schema of the stored procedures of several databases.

Is there a way to put the alter schema statement within a loop that automaticaly processes all the stored procedures in a given database ?

thank you

Probably your best option is to use a cursor. You can find more information about them in BOL (http://msdn2.microsoft.com/en-us/library/ms180169.aspx)

-Raul Garcia

SDE/T

SQL Server Engine

|||

You can also try doing something like this. If NEWSCHEMA is the schema you want to transfer all the procedures to the following query should help

declare @.querystring nvarchar(MAX)

set @.querystring=''

select @.querystring=@.querystring+' ALTER SCHEMA NEWSCHEMA TRANSFER ' + schema_name(schema_id) + '.' + name from sys.procedures

exec(@.querystring)

Either way, you will have to use dynamic sql.

Alter View Hangs - Merge Replication SQL 2005

Background - I have a publication that propigates schema changes. I have a view in which I want to remove a column.

Error - Going by what the BOL says, I use Alter View and delete the column from my select statement. I issue the alter view command against the Publication database and it just "churns". I do not get any locking errors or any other type of error, but the statement never completes execution. I watched it run for 10 minutes and cancelled the query. Executing the same statement against a copy of the database that is not being published executes in 1, 2 seconds.

Here is what I am doing:

Old View: Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc,

table2.txt_sort_order

FROM table1 join table2 on table1.status_code = table2.status_code

The query I am executing:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER VIEW myview

AS

Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc

FROM table1 join table2 on table1.status_code = table2.status_code

If this view is the only article in the publication, then it is a known issue.

Add a dummy table to the publication and your alter should succeed.

ALTER TABLE to Allow Null Values

I have an (Access 2003) database and I'm trying to update the schema of the database to allow null values in a column. The column already exists and currently will not allow null values. This is a distributed application (everyone has their own different MDB file) so I need to be able to modify the column through T-SQL.

My statement to try and do this is:
ALTER TABLE clients ALTER COLUMN state VARCHAR(255) NULL

However, when I view the table after running that SQL statement the table is still not allowing null values. Please don't tell me I need to drop the column before allowing null values.

Thanks,
Ryan

> I have an (Access 2003) database

Do you realize this group is about SQL Server?

AMB

|||Nope I just thought it was about T-SQL I didn't see that it was a sub-group of SQL Server. Sorry.
|||

No need to apologize.

There are differences between Access-SQL and T-SQL.

And of course, some Access applications use SQL Server for the backend (ADP Projects.) So at times, this would be the correct forumn. But for your particular question, one of the many Access forumns or NNTP groups 'might' be a better choice.

ALTER TABLE statement error while rebuilding schema

I am running a product that is rebuilding several tables. On one table, after re-creating a table (afm_groups) with a new primary key constraint, I run the following line:
ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name)
go
And get the error:
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'afm_flds_edit_group'.
The conflict occurred in database 'Hq', table 'afm_groups', column 'group_name'
There is no foreign key constraint built like this with this statement is executed. Any ideas?
This means that you have data in the edit_group column that doesn't have a
match in the afm_groups table.
(A foreign key's value must exist in the parent table.)
To identify the rows that are violating the foreign key:
SELECT * FROM afm_flds WHERE edit_group NOT IN (SELECT group_name FROM
afm_groups)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"psrd66" <paul_doucette@.archibus.com> wrote in message
news:3B6C2338-2672-4D74-936B-17E3D728BEE4@.microsoft.com...
> I am running a product that is rebuilding several tables. On one table,
after re-creating a table (afm_groups) with a new primary key constraint, I
run the following line:
> ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
> FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name)
> go
> And get the error:
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'afm_flds_edit_group'.
> The conflict occurred in database 'Hq', table 'afm_groups', column
'group_name'
> There is no foreign key constraint built like this with this statement is
executed. Any ideas?
>
|||Thanks, Aaron.
You were right. I created an invalid data condition somehow.
Paul Doucette
ARCHIBUS, Inc.

ALTER TABLE statement error while rebuilding schema

I am running a product that is rebuilding several tables. On one table, aft
er re-creating a table (afm_groups) with a new primary key constraint, I run
the following line:
ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name)
go
And get the error:
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'afm_fld
s_edit_group'.
The conflict occurred in database 'Hq', table 'afm_groups', column 'group_na
me'
There is no foreign key constraint built like this with this statement is ex
ecuted. Any ideas?This means that you have data in the edit_group column that doesn't have a
match in the afm_groups table.
(A foreign key's value must exist in the parent table.)
To identify the rows that are violating the foreign key:
SELECT * FROM afm_flds WHERE edit_group NOT IN (SELECT group_name FROM
afm_groups)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"psrd66" <paul_doucette@.archibus.com> wrote in message
news:3B6C2338-2672-4D74-936B-17E3D728BEE4@.microsoft.com...
> I am running a product that is rebuilding several tables. On one table,
after re-creating a table (afm_groups) with a new primary key constraint, I
run the following line:
> ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
> FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name)
> go
> And get the error:
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'afm_flds_edit_group'.
> The conflict occurred in database 'Hq', table 'afm_groups', column
'group_name'
> There is no foreign key constraint built like this with this statement is
executed. Any ideas?
>|||Thanks, Aaron.
You were right. I created an invalid data condition somehow.
Paul Doucette
ARCHIBUS, Inc.sql

ALTER TABLE statement error while rebuilding schema

I am running a product that is rebuilding several tables. On one table, after re-creating a table (afm_groups) with a new primary key constraint, I run the following line
ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name
g
And get the error
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'afm_flds_edit_group'.
The conflict occurred in database 'Hq', table 'afm_groups', column 'group_name
There is no foreign key constraint built like this with this statement is executed. Any ideasThis means that you have data in the edit_group column that doesn't have a
match in the afm_groups table.
(A foreign key's value must exist in the parent table.)
To identify the rows that are violating the foreign key:
SELECT * FROM afm_flds WHERE edit_group NOT IN (SELECT group_name FROM
afm_groups)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"psrd66" <paul_doucette@.archibus.com> wrote in message
news:3B6C2338-2672-4D74-936B-17E3D728BEE4@.microsoft.com...
> I am running a product that is rebuilding several tables. On one table,
after re-creating a table (afm_groups) with a new primary key constraint, I
run the following line:
> ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
> FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name)
> go
> And get the error:
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'afm_flds_edit_group'.
> The conflict occurred in database 'Hq', table 'afm_groups', column
'group_name'
> There is no foreign key constraint built like this with this statement is
executed. Any ideas?
>

ALTER TABLE ALTER COLUMN [access-id] failed because one or more objects access this column

Hi

when I'm upgrading table schema with alter statement

I'm getting error like this

ALTER TABLE ALTER COLUMN [access-id] failed because one or more objects access this column

can Anybody tell the solutiuon plz.

Thank u .

vizai

Please check to see if you have foreign key(s) referencing the column.|||

hi Waldrop

The column has a constraint

|||

If you have any View /UDF With SchemaBinding on this Base table , you are not allowed to change or Drop columns or Table Object..

If you want to alter the table first you need to remove the SchemaBinding in all the views/UDF and change the column on Base table then recreate the Views/UDFs With SchemaBinding.

If you have any Indexed Views when you remove the SchemaBinding all the Index on the Views will be removed, so you have to create them too..

|||

Hi mani

Thanks fo the reply.

How can I use schemabinding to drop the views / udf / indexed views using SMO.

How can I generate the drop script alone for all the views / udf / indexed views / constraints.

Can u help me in this

ALTER TABLE ALTER COLUMN [access-id] failed because one or more objects access this column

Hi

when I'm upgrading table schema with alter statement

I'm getting error like this

ALTER TABLE ALTER COLUMN [access-id] failed because one or more objects access this column

can Anybody tell the solutiuon plz.

Thank u .

vizai

Please check to see if you have foreign key(s) referencing the column.|||

hi Waldrop

The column has a constraint

|||

If you have any View /UDF With SchemaBinding on this Base table , you are not allowed to change or Drop columns or Table Object..

If you want to alter the table first you need to remove the SchemaBinding in all the views/UDF and change the column on Base table then recreate the Views/UDFs With SchemaBinding.

If you have any Indexed Views when you remove the SchemaBinding all the Index on the Views will be removed, so you have to create them too..

|||

Hi mani

Thanks fo the reply.

How can I use schemabinding to drop the views / udf / indexed views using SMO.

How can I generate the drop script alone for all the views / udf / indexed views / constraints.

Can u help me in this

ALTER DATABASE and schema bound views.

Hello, everyone!
The question is: Can I alter the collation of a database
inside which there is a schema bound view, under SQL
Server 2000?
If you could help me with it, I'd ve extremely thankful.
We've got a product which is commercialized
internationally.
In order to do so, we have a basic database, and we alter
its collation to fit the target market.
Lately, we added a materialized view, and the ALTER
DATABASE COLLATE... command stopped working.
This is part of the script:
SELECT DATABASEPROPERTYEX('db', 'Collation') --
'SQL_Latin1_General_CP1255_CI_AS'
GO
DROP VIEW [DBO].[MATERIAL_VIEW_001]
GO
CREATE VIEW [DBO].[MATERIAL_VIEW_001]
WITH SCHEMABINDING
AS
SELECT DV.[DEPARTMENT], -- char(10)
DV.[FILENUM], -- tinyint
DV.[FIELDNUM], -- smallint
DV.[FROM_DATE], -- datetime
DV.[VALUE], -- varchar(1024), usually <
20.
EI.[DEP_INF] -- tinyint
FROM [DBO].[DEPVAL] DV WITH (NOLOCK)
JOIN [DBO].[EMPINF] EI WITH (NOLOCK)
ON EI.FILENUM = DV.FILENUM -- tinyint
AND EI.FIELDNUM = DV.FIELDNUM -- smallint
WHERE EI.DEP_INF > 0 -- tinyint
GO
CREATE UNIQUE CLUSTERED INDEX PK_MATERIAL_VIEW_001
ON DBO.[MATERIAL_VIEW_001]
( [DEPARTMENT], [FILENUM], [FIELDNUM], [FROM_DATE],
[DEP_INF], [VALUE] )
GO
-- drop index lv_depval_inf.pk_MATERIAL_VIEW_001 -- This
doesn't help.
ALTER DATABASE DB COLLATE French_CI_AS
/*
Server: Msg 5075, Level 16, State 1, Line 1
The object 'MATERIAL_VIEW_001' is dependent on database
collation.
Server: Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of
database 'db' cannot be set to French_CI_AS.
*/
Can you help me with this?
Thanks in advance,
PabloHi
You don't mention if you change the column collations? e.g
http://tinyurl.com/91sg
I would also expect you to check what collation the system is set to:
Select SERVERPROPERTY(N'Collation')
I would expect the way around this is to drop and re-create the view after
you have changed the collation.
John
"Pablo Aliskevicius" wrote:

> Hello, everyone!
> The question is: Can I alter the collation of a database
> inside which there is a schema bound view, under SQL
> Server 2000?
> If you could help me with it, I'd ve extremely thankful.
> We've got a product which is commercialized
> internationally.
> In order to do so, we have a basic database, and we alter
> its collation to fit the target market.
> Lately, we added a materialized view, and the ALTER
> DATABASE COLLATE... command stopped working.
> This is part of the script:
> SELECT DATABASEPROPERTYEX('db', 'Collation') --
> 'SQL_Latin1_General_CP1255_CI_AS'
> GO
> DROP VIEW [DBO].[MATERIAL_VIEW_001]
> GO
> CREATE VIEW [DBO].[MATERIAL_VIEW_001]
> WITH SCHEMABINDING
> AS
> SELECT DV.[DEPARTMENT], -- char(10)
> DV.[FILENUM], -- tinyint
> DV.[FIELDNUM], -- smallint
> DV.[FROM_DATE], -- datetime
> DV.[VALUE], -- varchar(1024), usually <
> 20.
> EI.[DEP_INF] -- tinyint
> FROM [DBO].[DEPVAL] DV WITH (NOLOCK)
> JOIN [DBO].[EMPINF] EI WITH (NOLOCK)
> ON EI.FILENUM = DV.FILENUM -- tinyint
> AND EI.FIELDNUM = DV.FIELDNUM -- smallint
> WHERE EI.DEP_INF > 0 -- tinyint
> GO
> CREATE UNIQUE CLUSTERED INDEX PK_MATERIAL_VIEW_001
> ON DBO.[MATERIAL_VIEW_001]
> ( [DEPARTMENT], [FILENUM], [FIELDNUM], [FROM_DATE],
> [DEP_INF], [VALUE] )
> GO
> -- drop index lv_depval_inf.pk_MATERIAL_VIEW_001 -- This
> doesn't help.
> ALTER DATABASE DB COLLATE French_CI_AS
> /*
> Server: Msg 5075, Level 16, State 1, Line 1
> The object 'MATERIAL_VIEW_001' is dependent on database
> collation.
> Server: Msg 5072, Level 16, State 1, Line 1
> ALTER DATABASE failed. The default collation of
> database 'db' cannot be set to French_CI_AS.
> */
> Can you help me with this?
>
> Thanks in advance,
> Pablo
>|||Thank you, John, for your answer.
Maybe I wasn't clear enough: the application I'm
supporting has dozens of installations in at least ten
countries (that I know of) spanning three continents (or
four, if you count South America and North America as
two). Two more countries will be added in the next few
months. As a result, the server collation can be just
about any.
The software works on top of SQL Server. Since the
software is alive, the database keeps changing: fields
are added to existing tables, new tables and procedures
are added from time to time, new views appear from time
to time. We have already dozens of tables, and over 300
procedures.
As a result, an automatic upgrade program was written,
which compares the production database at the client's
site, with a 'last model' database. This 'last model'
exists in one place only, with one collation only.
In order to compare the databases, the 'model' database
must assume the 'target' database's collation.
Since there are a LOT of objects, dropping and recreating
objects can be done only as a last resource. A way to
execute ALTER DATABASE even when a schema-bound view
exists would be the best possible solution. After that, I
run a script quite like the one described in the URL you
mention - but the DATABASE_DEFAULT is UNKNOWN until run
time.
Thanks again,
Pablo.

>--Original Message--
>Hi
>You don't mention if you change the column collations?
e.g
>http://tinyurl.com/91sg
>I would also expect you to check what collation the
system is set to:
>Select SERVERPROPERTY(N'Collation')
>I would expect the way around this is to drop and re-
create the view after
>you have changed the collation.
>John
>

Alter coulmn (Replication applied)

Hi All
I dont know how to modify the table schema (e.g. Alter Column) without
removing the applied replication. Would anyone please tell me the solution?
Thanks alot,
Douglas
Douglas,
With SQL2000 you can add/drop columns in a replicated table using
sp_repladdcolumn and sp_repldropcolumn but there is no facility to alter
them. One way to accomplish this would be to unsubscribe, unpublish and then
do the alter table...alter column followed by republishing and
resubscribing. One more way would be to add a new column with the required
format, update the new column with data from the column which was meant to
be altered and then drop it, all without unpublishing. I havent tried the
latter option but a quick guess on the amount of work to be done, I think it
will be a pain.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"douglas" <douglaswong@.hotmail.com> wrote in message
news:#tcohAGREHA.2932@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I dont know how to modify the table schema (e.g. Alter Column) without
> removing the applied replication. Would anyone please tell me the
solution?
> Thanks alot,
> Douglas
>

Alter coulmn (Replication applied)

Hi All
I dont know how to modify the table schema (e.g. Alter Column) without
removing the applied replication. Would anyone please tell me the solution?
Thanks alot,
DouglasDouglas,
With SQL2000 you can add/drop columns in a replicated table using
sp_repladdcolumn and sp_repldropcolumn but there is no facility to alter
them. One way to accomplish this would be to unsubscribe, unpublish and then
do the alter table...alter column followed by republishing and
resubscribing. One more way would be to add a new column with the required
format, update the new column with data from the column which was meant to
be altered and then drop it, all without unpublishing. I havent tried the
latter option but a quick guess on the amount of work to be done, I think it
will be a pain.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"douglas" <douglaswong@.hotmail.com> wrote in message
news:#tcohAGREHA.2932@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I dont know how to modify the table schema (e.g. Alter Column) without
> removing the applied replication. Would anyone please tell me the
solution?
> Thanks alot,
> Douglas
>

Alter coulmn (Replication applied)

Hi All
I dont know how to modify the table schema (e.g. Alter Column) without
removing the applied replication. Would anyone please tell me the solution?
Thanks alot,
DouglasDouglas,
With SQL2000 you can add/drop columns in a replicated table using
sp_repladdcolumn and sp_repldropcolumn but there is no facility to alter
them. One way to accomplish this would be to unsubscribe, unpublish and then
do the alter table...alter column followed by republishing and
resubscribing. One more way would be to add a new column with the required
format, update the new column with data from the column which was meant to
be altered and then drop it, all without unpublishing. I havent tried the
latter option but a quick guess on the amount of work to be done, I think it
will be a pain.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"douglas" <douglaswong@.hotmail.com> wrote in message
news:#tcohAGREHA.2932@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I dont know how to modify the table schema (e.g. Alter Column) without
> removing the applied replication. Would anyone please tell me the
solution?
> Thanks alot,
> Douglas
>

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