Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
McDale
Starting Member
8 Posts |
 Posted - 2007-11-08 : 18:12:28
|
| Hey there,I am getting an error:"UNION ALL view 'FACT_CUSTOMER_TRANS' is not updatable because a partitioning column was not found."when trying to update this horizontal partitioning setup. Trouble is, everything that I can see easily looks OK. I can't find any overlaps in the check constraints, the column is an int representing a date (YYYYMMDD) and is part 1 of 4 of the primary key. The partitions are based on Fiscal Period date ranges.1)Though unlikely, I am thinking maybe the tables aren't exactly the same? Does anyone know of a quick way to compare tables? (ie: There are 51 member tables, with many columns!)2)As we were partitioning data, we had the view connected to the old table with a where clause we changed as more data got partitioned. We dropped and recreated the view after completing the partitioning, but could this be why its not working?3)Lastly, with an integer are "Gaps" allowed in the check constraints? For example, at the end of each fiscal year. One table's last records will be for 20070331, and the first for the next one will be 20070401.So, if table 1's constraint is BETWEEN 200703XX and 20070331and table 2's constriant is BETWEEN 20070401 and 20070XXXis this a problem? or would I need to make table 1's end on a date that doesn't exits like 20070400?Please help! I can't seem to find the problem here. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-08 : 18:42:23
|
You might be able to use this or a variation. probably better ways, but can work. I would think the error is pretty clear in that a column has a different name (or one missing) in some way from what it expectsSELECT @tablename1, c.colid , c.name as colname, --uncomment below to get datatype and description info if you want it-- -- LEFT(CASE-- WHEN (T.name IN ('char', 'varchar', 'nchar','nvarchar')) THEN T.name + '(' + LTRIM(RTRIM(STR(C.length))) + ')'-- When t.name in ('numeric','decimal') then t.name + '(' + Cast(c.Scale as varchar) + ','+ cast(c.Prec as varchar) + ')'-- else t.name -- END,30) as TypeData, -- Cast((SELECT TOP 1 value -- FROM ::fn_listextendedproperty(null, 'user', @user, @type, @tablename, 'column', default)-- WHERE objname = C.name) as varchar) as ColDescriptionInto #table1FROM sysobjects o left JOIN syscolumns c ON (o.id = c.id) left JOIN systypes t ON (c.xusertype = t.xusertype)WHERE o.name = @tablename1 ORDER BY C.colidSELECT @tablename2, c.colid , c.name as colname, --uncomment below to get datatype and description info if you want it-- -- LEFT(CASE-- WHEN (T.name IN ('char', 'varchar', 'nchar','nvarchar')) THEN T.name + '(' + LTRIM(RTRIM(STR(C.length))) + ')'-- When t.name in ('numeric','decimal') then t.name + '(' + Cast(c.Scale as varchar) + ','+ cast(c.Prec as varchar) + ')'-- else t.name -- END,30) as TypeData, -- Cast((SELECT TOP 1 value -- FROM ::fn_listextendedproperty(null, 'user', @user, @type, @tablename, 'column', default)-- WHERE objname = C.name) as varchar) as ColDescriptionInto #table2FROM sysobjects o left JOIN syscolumns c ON (o.id = c.id) left JOIN systypes t ON (c.xusertype = t.xusertype)WHERE o.name = @tablename2 ORDER BY C.colidSelect colname from #table1 where colname not in (Select colname from #table2) |
 |
|
|
McDale
Starting Member
8 Posts |
Posted - 2007-11-09 : 13:26:05
|
| I will give it a try. Thank you.Another thing I was going to try today is create a second view and slowly add more and more tables into it until I find ones that are causing problems. |
|
|
|
McDale
Starting Member
8 Posts |
Posted - 2007-11-09 : 16:36:45
|
Hey there. Double checked and all tables are the same, and all check constraints are fine.I also built a test view and found that there are two specific member tables that break the "updateableness" of the view.So, I ran a DMO table check script I had kicking around comparing these tables. It looks like the only difference that I can find is on the two tables that aren't working properly, there are some extra "_WA_Sys" indexes. From what I've read, these are used by SQL Server statistics. In all the other tables, the only _WA_Sys index is on one column (the same on each). In the other two, the same one is there, but on one there is one extra and on the other there are several extra.Could it be these "Sys" indexes that are messing up the Horizontal Partition?Is there any way around this??? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-09 : 17:08:13
|
| DBCC CheckDB ('Database')DBCC UpdateUsage ('Database')DBCC REINDEX 'Table'come to mind ...Or manage your indexes, drop them all and recreate...reindex to resolve the issue. |
|
|
|
McDale
Starting Member
8 Posts |
Posted - 2007-11-09 : 17:17:18
|
| The only indexes on these tables is the PK/Clustered index which is the same on each, so I don't think "reindexing" will do anything will it?Is there anyway to clear out these Sys indexes? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-09 : 17:27:52
|
| http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic13776.aspxthat link seems to address the identical issue, see if that info helps out rather than me retype it |
|
|
|
McDale
Starting Member
8 Posts |
Posted - 2007-11-13 : 13:00:03
|
| Hey, thanks for the information. It's good to know some more details on how the statistics are used.However, I found the only way I could fix this in order for it to work correctly again was to drop and re-add the clustered Primary Key on the two member tables that had the issue. This fixed the partition scheme so that it was updatable, and the error"UNION ALL view 'FACT_CUSTOMER_TRANS' is not updatable because a partitioning column was not found."went away.Our ETL tool (Cognos DecisionStream) is setup to deleiver (via a BCP delivery) directly to the newest member table each day. This is because you can't BCP to the view. However, now Monday morning, my update code to fix some historical information gets the same error from above again. I tested using a test view removing the newest member table from the union all view again, and it works fine.I am going to try to drop and re-create the PK on the newest member table again and see if that again solves the issue temporarily.So, it seems the daily updates are causing issues with the "updatableness" of the view. Is there any way around this? I suppose we are going to have to stop using BCP as the loading mechanism to solve this? |
|
|
|
McDale
Starting Member
8 Posts |
Posted - 2007-11-13 : 18:44:45
|
| Just confirmed, it is not the re-creation of the Clustered Index that is causing the problem. It is the Check Constraint. If I drop and re-create the same constraint, then the union all view updates work.Hope that helps. Any ideas? |
|
|
|
McDale
Starting Member
8 Posts |
Posted - 2007-11-14 : 14:47:53
|
| I'm trying an option I found, as it looks like BCP by default ignores check constraints.-h "CHECK_CONSTRAINTS"Will update with whether or not this worked. |
|
|
|
|
|
|
|
|
Horizontal Partitions not updatable - Check?