473,698 Members | 2,943 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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. Hummel
Jul 20 '05 #1
3 3803
Thomas R. Hummel (to********@hot mail.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
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****@sommars kog.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.

Jul 20 '05 #3
Thomas R. Hummel (to********@hot mail.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1473
by: Peder Bacher | last post by:
Hello :-) My question is: If I query a partitioned view, but don't know the values in the "where x in(<expression>)" clause, i.e.: select * from viewA where intVal in(select intVal from tbl1) . Compared to: select * from viewA where intVal in(5,6). Of course "intVal" is partitioning column. Will this result in an optimized query that searches only the relevant tables?
4
2714
by: stacdab | last post by:
We have a partitioned view with 4 underlying tables. The view and each of the underlying tables are in seperate databases on the same server. Inserts and deletes on the view work fine. We then add insert and delete triggers to each of the underlying tables. The triggers modify a different set of tables in the same database as the view (different than the underlying table). The problem is those triggers aren't fired when inserting or...
2
4322
by: Wayne | last post by:
I've just performed a reformat and reinstalled Access 2000 and 2003 and as usual the linked table manager in Access 2003 is broken ie. it doesn't show any tables. I've gone through the process of re-registering Accwiz.dll as outlined in the MS Knowledge Base and although this process has worked for me before, it hasn't worked this time. There are still no tables showing. The article in the knowledge base includes this line: "If the...
0
1283
by: MarsLandingParty | last post by:
Hello All, I have managed to create a partitioned view ona large table - the view is updateable, and looking at the execution plan, only the relevant table is queried. The partition is by month - we have a date dimension that I join to this table in queries e.g. query below, and again - only one table is scanned SELECT COUNT(*) FROM VF_Fact_Call_Rec a INNER JOIN date_Dim b ON a.DateID=b.DateID
2
1805
by: sudhashekhar30 | last post by:
hi all i am using form view. i want to display record in form view from different table. like 1rst time its showing record from tableemp. 2nd time i want to show record of tableSalary in same form view. based upon some conditions. so i want to bind data in codebehind file. how to do it? i am using formview 1rst time. i am using c# code. thanks. regards sudha
4
6307
by: smanojgroup | last post by:
I have a view defined as select * on base table. When base table is altered to add new column, new column does not appear when selected from view. Here is what I did to test. db2 "create table test_sch.test ( c1 smallint, c2 smallint)" db2 "insert into test_sch.test values(1,1)" db2 "insert into test_sch.test values(2,2)" db2 "create view test_sch.test_v as select * from test_sch.test"
1
1341
by: nitdesh | last post by:
Hi , I have Just Registered to This Site and Deeply Require a Help For my Software . I am Into Warehousing Business I Have Front End as Visual Basic 6.0 and Backend as SQl 7.0 Database Name : deshmukhwarehousing Within Which I have table as GR : Good Recived
1
5651
by: Rohullah | last post by:
Hello sir I want to view the query of a created table. for example i create a table like this ------------------------- create table emp (empno number primary key, Ename varchar2(50), Address varchar2(250), Phone varchar2(8),
2
1315
by: raju selvam | last post by:
How to view the access table details in DataGrid?
0
8683
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9031
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
7740
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5862
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4372
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4623
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3052
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2339
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2007
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.