SQL Server Performance Forum – Threads Archive
About SQL Server partition view
I amn’t sure whether it’s a bug of SQL Server. When I created the tables for a partition view in the Enterprise Manager,and key in some data to the view,it showed me a error message like this:[Microsoft][ODBC SQL Server]UNION ALL VIEW "view_name" Can’t be updated,because can’t find the column which be partitioned. but when I create/modify the tables with T-SQL ,it will be successful. Many thanks for any help
Jelly.
Is it possible to post the schema of the partitioned view and tables, so we can attempt try reproduce?
When you create a partioned view, at lease one of the column in the tables should have a constraint which is used for partitioning. So if I have a dataset of values for a column col1 as 1-3000 and I want to partition the table into three tables on the basis of this column, I should create three tables having constraint on col1 as values < 1000, between 1001-2000 and >2000 respectively. This way when u want to insert data in view, the query engine knows where to insert the data. I guess this is the problem that you r facing. Check the table definations of the partitions. HTH. Gaurav
Yes there is a problem with Enterprise Manager especially when you perform schema changes on your tables. In Enterprise Manager, the code that adds the CHECK constraint to the tables uses the WITH NOCHECK option. SQL Server tracks whether a constraint was added WITH NOCHECK by turning on or off the twelfth bit in sysobjects.status field for the constraint’s row. When the bit is on, SQL Server knows that it can’t trust that all the rows in the table meet the constraint’s criteria. That is why you are getting the error. Use T-SQL (Query Analyzer) to create your CHECK constraints as the SEM does it inefficiently.
The details for what vbkenya said can be found at http://www.sqlmag.com/Articles/Index.cfm?ArticleID=37889 HTH. Gaurav
Oh!!Thanks very much!! Jelly.
…And sorry for not putting up the link. I am still new to the whole stuff of forums and newsgroups. I’ll try to be more helpful next time.
You can create a instead of trigger for the view to insert the records depends on the check constraints specified while creating the table. Soma
]]>