Partitioned view broken after moving table to new filegroup

[Thomas R. Hummel]

I am using SQL Server 2000, SP3.

I created an updatable partitioned view awhile ago and it has been
running smoothly for some time. The partition is on a DATETIME column
and it is partitioned by month. Each month a stored procedure is
scheduled that creates the new month's table, and alters the view to
include it. Again... working like a charm for quite some time.

This past weekend I moved some of the first tables onto a new file
group. I did this through Enterprise Manager, by going into design
mode for the table, then going into the properties for the table and
changing the file group there as well as in all of the indexes. Now
the partitioned view is no longer updatable. It gives the error
message: "UNION ALL view '<view name>' is not updatable because a
partitioning column was not found."

I have extracted the DDL for all of the partition tables and compared
them and they all look the same. I checked and then double-checked the
CHECK constraints to make sure that they were all valid and they are.
If I remove the tables that I moved to the new file group from the
view, then it is once again updatable, but when I put them back in it
fails again.

Any ideas? If you would like samples of the code then I can send it
along, but it's rather large, so I have not included it here.

Thanks!
Thomas R. Hummel

[Erland Sommarskog]

Thomas R. Hummel (to********@hotmail.com) writes:

I created an updatable partitioned view awhile ago and it has been
running smoothly for some time. The partition is on a DATETIME column
and it is partitioned by month. Each month a stored procedure is
scheduled that creates the new month's table, and alters the view to
include it. Again... working like a charm for quite some time.

This past weekend I moved some of the first tables onto a new file
group. I did this through Enterprise Manager, by going into design
mode for the table, then going into the properties for the table and
changing the file group there as well as in all of the indexes. Now
the partitioned view is no longer updatable. It gives the error
message: "UNION ALL view '<view name>' is not updatable because a
partitioning column was not found."

I have extracted the DDL for all of the partition tables and compared
them and they all look the same. I checked and then double-checked the
CHECK constraints to make sure that they were all valid and they are.
If I remove the tables that I moved to the new file group from the
view, then it is once again updatable, but when I put them back in it
fails again.

Any ideas? If you would like samples of the code then I can send it
along, but it's rather large, so I have not included it here.

Without sample code, only wild guesses are possible.

But one possbility is that as part of the process the CHECK constraints
were disabled and then enabled without WITH CHECK. Then SQL Server does not
trust those constraints.

Try do:

ALTER TABLE tbl NOCHECK CONSTRAINT your_constraint
ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT your_constraint

Moral: don't use Enterprise Manager, if you don't know what it does
behind your back.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Thomas R. Hummel]

Thanks Erland! That was exactly the problem.

As a related followup... if I know that the data in the table
satisfies the constraint, can I make a change to sysobjects to set the
status for the check constraint object to "2"? I understand all of the
usual warnings about making changes to system tables. Just wondering
if it tracks the check constraint information anywhere else.

Since the column was part of the clustered primary key doing the check
on the constraint took less than 1 minute for a table of over 20M
rows, but it might be a useful trick if the check would take longer
than that.

Thanks again,
-Tom.

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

But one possbility is that as part of the process the CHECK constraints
were disabled and then enabled without WITH CHECK. Then SQL Server does not
trust those constraints.

Try do:

ALTER TABLE tbl NOCHECK CONSTRAINT your_constraint
ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT your_constraint

Moral: don't use Enterprise Manager, if you don't know what it does
behind your back.

[Erland Sommarskog]

Thomas R. Hummel (to********@hotmail.com) writes:

As a related followup... if I know that the data in the table
satisfies the constraint, can I make a change to sysobjects to set the
status for the check constraint object to "2"? I understand all of the
usual warnings about making changes to system tables. Just wondering
if it tracks the check constraint information anywhere else.
I for one would never try to do such a thing. It might work, but I don't
like unpleasant surprises later on.
Since the column was part of the clustered primary key doing the check
on the constraint took less than 1 minute for a table of over 20M
rows, but it might be a useful trick if the check would take longer
than that.

The clustered index does not have much to do it, but the non-clustered
indexes on the table were. Since the clustered index columns act as
row locator in the NC indexes, SQL Server could take a shortcut and scan
the smallest NC index, rather than the whole table.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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