particulat table in Filegroup

Dear Friends
I have a small database of 1 gb i have created some
Secondary data files for same. in the same database some
tables are extensively used for the Modification hence i
want to separte them form other table datafiles can i do
the same.
please suggest.
Best regards
NiitMaladWhat you can do is create multiple filegroups and assign a datafile to a
filegroup.
You can specify the create table statement with the filegroup it must be
created on
CREATE Table tbl_demo (COL1 INT)
on FILEGROUP
where filegroup is the logical name of the filegroup.
If you have an existing table and you want to place it on another filegroup
you can only create a new table with the ON filegroup keywords, insert from
the first table, delete the first table and rename the new table, what means
: there is no alter table statement to move a table to another filegroup.
You first need to create the filegroups before you create the datafiles and
assign them onto a filegroup
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"niitmalad" <niitmalad@.yahoo.co.in> wrote in message
news:006601c3dd18$6132f930$a101280a@.phx.gbl...

quote:

---

> Dear Friends
> I have a small database of 1 gb i have created some
> Secondary data files for same. in the same database some
> tables are extensively used for the Modification hence i
> want to separte them form other table datafiles can i do
> the same.
> please suggest.
> Best regards
> NiitMalad

---

|||> If you have an existing table and you want to place it on another
filegroup

quote:

---

> you can only create a new table with the ON filegroup keywords

---

Or you could create a clustered index using the ON filegroup clause, and it
will move the data to the filegroup. Of course, if there's already a
clustered index, you'll have to drop it first. Which complicates things if
it's a referenced primary key.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

particulat table in Filegroup

Dear Friends
I have a small database of 1 gb i have created some
Secondary data files for same. in the same database some
tables are extensively used for the Modification hence i
want to separte them form other table datafiles can i do
the same.
please suggest.
Best regards
NiitMaladWhat you can do is create multiple filegroups and assign a datafile to a
filegroup.
You can specify the create table statement with the filegroup it must be
created on
CREATE Table tbl_demo (COL1 INT)
on FILEGROUP
where filegroup is the logical name of the filegroup.
If you have an existing table and you want to place it on another filegroup
you can only create a new table with the ON filegroup keywords, insert from
the first table, delete the first table and rename the new table, what means
: there is no alter table statement to move a table to another filegroup.
You first need to create the filegroups before you create the datafiles and
assign them onto a filegroup
--
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"niitmalad" <niitmalad@.yahoo.co.in> wrote in message
news:006601c3dd18$6132f930$a101280a@.phx.gbl...
> Dear Friends
> I have a small database of 1 gb i have created some
> Secondary data files for same. in the same database some
> tables are extensively used for the Modification hence i
> want to separte them form other table datafiles can i do
> the same.
> please suggest.
> Best regards
> NiitMalad|||> If you have an existing table and you want to place it on another
filegroup
> you can only create a new table with the ON filegroup keywords
Or you could create a clustered index using the ON filegroup clause, and it
will move the data to the filegroup. Of course, if there's already a
clustered index, you'll have to drop it first. Which complicates things if
it's a referenced primary key.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

PARTIAL SQL Server RESTORE's

Please..
Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
have to have a Full Database Backup (containing all the Filegroups including
the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
Even if you have a performed a filegroup Backup which contains the PRIMARY
filegroup and all the other filegroups then it will not work and you get the
error message:
Server: Msg 3135, Level 16, State 2, Line 1
"was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
restore operation."
All the examples I have seen on the net use a FULL Database backup as a
starting point and I have seen the following posting which kind of confirms
my suspisions from someone with a microsoft.com email address
http://groups.google.com/groups?q=pa...TNGP12&rnum=25
Furtheremore, all the documentation in BOL is very very murky on this
particular subject area:
Partial Database Restore Operations
"Partial restore operations work with database filegroups. The primary
filegroup is always restored, along with the files that you specify and
their corresponding filegroups. The result is a subset of the database.
Filegroups that are not restored are marked as offline and are not
accessible.
Partial restore operations are accomplished with the PARTIAL clause of the
RESTORE statement. You can also use the PARTIAL option when restoring a full
database backup. Partial database restore of file backups is not supported."
What it says is "Partial database restore of file backups is not supported"
I think it should ALSO say "Partial database restore of filegroup and file
backups is not supported"
Many Thanks
Paul McMillan
Paul,
I agree with your points, and this is how I understand that things work as well. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). Did you perform the Books Online
feedback on this (the envelope top left in the right pane)?
USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='c:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='c:\mw.dat2')
TO FILEGROUP sales
GO
CREATE TABLE mywind..t2 (id int) ON sales
-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:
BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
TO DISK ='c:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
GO
BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO
-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:
-- RESTORE FILELISTONLY FROM DISK='c:\mywind.dmp'
-- GO
-- RESTORE HEADERONLY FROM DISK='c:\mywind.dmp'
-- GO
-- The RESTORE DATABASE statement restores the database under a different name
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log),
-- and all files in the restored filegroup (in this example, mywind_data_2 is the
-- only file in sales) are moved to a new location. The log is then recovered:
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='c:\mywind.dmp'
WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'c:\mw2.pri',
MOVE 'mywind_log' TO 'c:\mw2.log',
MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO
-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO
--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@.email4u.com> wrote in message news:%23xZPXcXTEHA.2716@.tk2msftngp13.phx.gbl...
> Please..
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups including
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get the
> error message:
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirms
> my suspisions from someone with a microsoft.com email address
>
http://groups.google.com/groups?q=pa...TNGP12&rnum=25
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a full
> database backup. Partial database restore of file backups is not supported."
> What it says is "Partial database restore of file backups is not supported"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
> Many Thanks
> Paul McMillan
>
|||Tibor
Thanks - As you get the same, it proves I am not going mad and I tried your
code with the same results. So I believe the documentation is a bit
misleading in this area...
Re: Did you perform the Books Online feedback on this (the envelope top left
in the right pane)?
No - But I have now! - outlining what I believe is unclear documentation.
Thanks again
Paul

PARTIAL SQL Server RESTORE's

Please..
Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
have to have a Full Database Backup (containing all the Filegroups including
the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
Even if you have a performed a filegroup Backup which contains the PRIMARY
filegroup and all the other filegroups then it will not work and you get the
error message:
Server: Msg 3135, Level 16, State 2, Line 1
"was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
restore operation."
All the examples I have seen on the net use a FULL Database backup as a
starting point and I have seen the following posting which kind of confirms
my suspisions from someone with a microsoft.com email address
http://groups.google.com/groups?q=partial+sql+server+restore+full+backup&start=20&hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25
Furtheremore, all the documentation in BOL is very very murky on this
particular subject area:
Partial Database Restore Operations
"Partial restore operations work with database filegroups. The primary
filegroup is always restored, along with the files that you specify and
their corresponding filegroups. The result is a subset of the database.
Filegroups that are not restored are marked as offline and are not
accessible.
Partial restore operations are accomplished with the PARTIAL clause of the
RESTORE statement. You can also use the PARTIAL option when restoring a full
database backup. Partial database restore of file backups is not supported."
What it says is "Partial database restore of file backups is not supported"
I think it should ALSO say "Partial database restore of filegroup and file
backups is not supported"
Many Thanks
Paul McMillanPaul,
I agree with your points, and this is how I understand that things work as well. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). Did you perform the Books Online
feedback on this (the envelope top left in the right pane)?
USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='c:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='c:\mw.dat2')
TO FILEGROUP sales
GO
CREATE TABLE mywind..t2 (id int) ON sales
-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:
BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
TO DISK ='c:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
GO
BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO
-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:
-- RESTORE FILELISTONLY FROM DISK='c:\mywind.dmp'
-- GO
-- RESTORE HEADERONLY FROM DISK='c:\mywind.dmp'
-- GO
-- The RESTORE DATABASE statement restores the database under a different name
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log),
-- and all files in the restored filegroup (in this example, mywind_data_2 is the
-- only file in sales) are moved to a new location. The log is then recovered:
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='c:\mywind.dmp'
WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'c:\mw2.pri',
MOVE 'mywind_log' TO 'c:\mw2.log',
MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO
-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO
--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@.email4u.com> wrote in message news:%23xZPXcXTEHA.2716@.tk2msftngp13.phx.gbl...
> Please..
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups including
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get the
> error message:
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirms
> my suspisions from someone with a microsoft.com email address
>
http://groups.google.com/groups?q=partial+sql+server+restore+full+backup&start=20&hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a full
> database backup. Partial database restore of file backups is not supported."
> What it says is "Partial database restore of file backups is not supported"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
> Many Thanks
> Paul McMillan
>|||Tibor
Thanks - As you get the same, it proves I am not going mad and I tried your
code with the same results. So I believe the documentation is a bit
misleading in this area...
Re: Did you perform the Books Online feedback on this (the envelope top left
in the right pane)?
No - But I have now! - outlining what I believe is unclear documentation.
Thanks again
Paul

PARTIAL SQL Server RESTORE's

Please..
Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
have to have a Full Database Backup (containing all the Filegroups including
the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
Even if you have a performed a filegroup Backup which contains the PRIMARY
filegroup and all the other filegroups then it will not work and you get the
error message:
Server: Msg 3135, Level 16, State 2, Line 1
"was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
restore operation."
All the examples I have seen on the net use a FULL Database backup as a
starting point and I have seen the following posting which kind of confirms
my suspisions from someone with a microsoft.com email address
&start=20&
hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25" target="_blank">http://groups.google.com/groups? q=...FTNGP12&rnum=25
Furtheremore, all the documentation in BOL is very very murky on this
particular subject area:
Partial Database Restore Operations
"Partial restore operations work with database filegroups. The primary
filegroup is always restored, along with the files that you specify and
their corresponding filegroups. The result is a subset of the database.
Filegroups that are not restored are marked as offline and are not
accessible.
Partial restore operations are accomplished with the PARTIAL clause of the
RESTORE statement. You can also use the PARTIAL option when restoring a full
database backup. Partial database restore of file backups is not supported."
What it says is "Partial database restore of file backups is not supported"
I think it should ALSO say "Partial database restore of filegroup and file
backups is not supported"
Many Thanks
Paul McMillanPaul,
I agree with your points, and this is how I understand that things work as w
ell. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). D
id you perform the Books Online
feedback on this (the envelope top left in the right pane)?
USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='c:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='c:\mw.dat2')
TO FILEGROUP sales
GO
CREATE TABLE mywind..t2 (id int) ON sales
-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:
BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
TO DISK ='c:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
GO
BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO
-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:
-- RESTORE FILELISTONLY FROM DISK='c:\mywind.dmp'
-- GO
-- RESTORE HEADERONLY FROM DISK='c:\mywind.dmp'
-- GO
-- The RESTORE DATABASE statement restores the database under a different na
me
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log
),
-- and all files in the restored filegroup (in this example, mywind_data_2 i
s the
-- only file in sales) are moved to a new location. The log is then recover
ed:
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='c:\mywind.dmp'
WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'c:\mw2.pri',
MOVE 'mywind_log' TO 'c:\mw2.log',
MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO
-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO
--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@.email4u.com> wrote in message news:%23xZPXcXTEHA.2716@.tk2msft
ngp13.phx.gbl...
> Please..
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups includi
ng
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get t
he
> error message:
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirm
s
> my suspisions from someone with a microsoft.com email address
>
&start=20&hl=en&lr=&ie=UTF
-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25" target="_blank">http://groups.google.com/groups? q=...FTNGP12&rnum=25
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a fu
ll
> database backup. Partial database restore of file backups is not supported
."
> What it says is "Partial database restore of file backups is not supported
"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
> Many Thanks
> Paul McMillan
>|||Tibor
Thanks - As you get the same, it proves I am not going mad and I tried your
code with the same results. So I believe the documentation is a bit
misleading in this area...
Re: Did you perform the Books Online feedback on this (the envelope top left
in the right pane)?
No - But I have now! - outlining what I believe is unclear documentation.
Thanks again
Paul

Partitioned view broken after moving table to new filegroup

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. HummelThomas R. Hummel (tom_hummel@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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 <esquel@.sommarskog.se> wrote in message news:<Xns9590ED2AA1AC6Yazorman@.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.|||Thomas R. Hummel (tom_hummel@.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, esquel@.sommarskog.se

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