June 3, 2008 at 7:50 am
Hello,
After our companies dba created partioning we can't remove three specific primary keys within three different tables. We are running sql 2005 server.
On the dbo.phonenumber table I´ll choose modify, and I try to remove the primary key named "repl". I´ll close the window en choose yes to save, right after that the following post-save Notification appears:
"Errors were encoutered during the save process. Some database object were not saved:
"Phonenumber" table
- Unable to modify table.
Partition column(s) of table don't match partition scheme sch_phonenumber
We have to delete these three keys but I'm stuck. I checked the rights, changed the name and changed the value but no succes.. The original dba is no longer working here.
Any thoughts? Thanks in advance!
June 3, 2008 at 8:05 am
- Keep in mind the concept of a primary key ! There must have been a reason to use this column for it ( or as part of it)
- Check out your partitioning scheme "sch_phonenumber" .
Best is to investigate "partitioning" in books online !
- Keep in mind this concerns physical organisation of your data, spread over multiple files.
Think safe: start with a full database backup !!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 3, 2008 at 11:17 am
What that error means is that the primary key of the table is part of the key for the partition. That means it's not a permissions issue. First, you would have to undo the partitioning, before you could remove the primary key.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 13, 2008 at 7:48 am
Mucho gracies for the replies... I am pretty new to SQL so I was wondering if there is a easy (free, without purchasing tools) way to remove the partioning...
I still got the file where is described how to rebuild the partioning:
step 1 = create indexes
step 2 = create index with move to primary (ie: unpartition back nto 1 file!)
step 3 = remove files en filegroups.
etc... The previous DBA made this plan including sql statements, but no explanation about how to remove to get the three primary keys gone...
I have searched but didn't find anything I could use..
June 18, 2008 at 10:57 am
Anyone??
June 19, 2008 at 2:06 am
Books online states:
Converting a Partitioned Table to a Nonpartitioned Table
Changing a partitioned table to a nonpartitioned table simply involves modifying the partition function of a partitioned table so that the table is made up of only one partition. Although this technically is still a partitioned table, this status is irrelevant to any subsequent operations you want to perform on the table.
If the table has a partitioned clustered index applied to it, you can obtain the same result by dropping the index and rebuilding it as a nonpartitioned index. You can do this by using the Transact-SQL CREATE INDEX command with the DROP EXISTING = ON clause.
To convert a partitioned table to a standard table with no partitions
ALTER PARTITION FUNCTION (Transact-SQL)
CREATE INDEX (Transact-SQL)
Prepare this action, because most of the times, partitioned tables are large (datasize) tables.
Make sure your tables filegroup is large enough, your logfile is large enough, and your index filegroup is large enough to support this action.
Play it safe, start with a full backup !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 27, 2008 at 9:03 pm
Hi,
What is the error are you getting?
Thanks -- Vj
July 9, 2008 at 8:22 am
Well I have to drop the whole partioning or I have to delete the partition function and it depending partition scheme so afterwards I can remove the primary keys on the table. Dont know what is best to do.
When I try to drop the partition function I get the error:
Msg 7717, Level 16, State 1, Line 1
Partition Function 'part_name' is currently being used to partition one or more tables.
I tried to use DROP PARTITION FUNCTION 'name' CASCADE but I don't get any result back from the query...
So my main question is, how can I delete the whole partitioning on the database? Or how can I remove the partitioning on the three tables???
July 9, 2008 at 2:08 pm
you can view the partitioned objects using these queries:
USE [DPartitioning]
/*
show partitioned objects
*/
select distinct
p.[object_id],
TbName = OBJECT_NAME(p.[object_id]),
index_name = i.[name],
index_type_desc = i.type_desc,
partition_scheme = ps.[name],
data_space_id = ps.data_space_id,
function_name = pf.[name],
function_id = ps.function_id
from sys.partitions p
inner join sys.indexes i
on p.[object_id] = i.[object_id]
and p.index_id = i.index_id
inner join sys.data_spaces ds
on i.data_space_id = ds.data_space_id
inner join sys.partition_schemes ps
on ds.data_space_id = ps.data_space_id
inner JOIN sys.partition_functions pf
on ps.function_id = pf.function_id
--WHERE p.[object_id] = object_id('mytable')
GO
/*
show partitioned objects range values
*/
select p.[object_id],
OBJECT_NAME(p.[object_id]) AS TbName,
p.index_id,
p.partition_number,
p.rows,
index_name = i.[name],
index_type_desc = i.type_desc,
i.data_space_id,
ds1.NAME AS [FILEGROUP_NAME],
pf.function_id,
pf.[name] AS Pf_Name,
pf.type_desc,
pf.boundary_value_on_right,
destination_data_space_id = dds.destination_id,
prv.parameter_id,
prv.value
from sys.partitions p
inner join sys.indexes i
on p.[object_id] = i.[object_id]
and p.index_id = i.index_id
inner JOIN sys.data_spaces ds
on i.data_space_id = ds.data_space_id
inner JOIN sys.partition_schemes ps
on ds.data_space_id = ps.data_space_id
inner JOIN sys.partition_functions pf
on ps.function_id = pf.function_id
inner join sys.destination_data_spaces dds
on dds.partition_scheme_id = ds.data_space_id
and p.partition_number = dds.destination_id
INNER JOIN sys.data_spaces ds1
on ds1.data_space_id = dds.data_space_id
left outer JOIN sys.partition_range_values prv
on prv.function_id = ps.function_id
and p.partition_number = prv.boundary_id
-- WHERE p.[object_id] = object_id('mytable')
;
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 9, 2008 at 2:41 pm
We have to delete these three keys but I'm stuck. I checked the rights, changed the name and changed the value but no succes.. The original dba is no longer working here.
You've never said why you need to remove the keys? A bit of background on the end result you are trying to achieve would help.
July 11, 2008 at 3:33 am
John Rowan (7/9/2008)
You've never said why you need to remove the keys? A bit of background on the end result you are trying to achieve would help.
The result I am trying to achieve is to get back to the situation before the partitioning. The reason for this is a update from the application designer wich cannot work with the three primary keys. All primary keys need to be gone otherwise the update cant be installed.
In my opion I have to delete the whole partioning of the tables where the primary key is on and after that I have to delete the primary keys for the update to run.
I tried the query from two post ago to gather the needed information and I tried this query to delete the partitioning:
ALTER TABLE phonelist DROP PARTITION phonelist
The following error states:
Msg 3728, Level 16, State 1, Line 1
'PARTITION' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
Am I using the wrong query??
July 11, 2008 at 5:17 am
did you try to drop the full PK constraint ?
ALTER TABLE your_partitioned_table
DROP CONSTRAINT PK_ ... '_constraintname
You may not be able to drop the PK if you have foreign keys that refer to it ! (so you may temporary have to remove DRI)
Be carefull, dropping the primary key should only be done during transition, for a very short period, without other applications using the table and with added back with check !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 17, 2008 at 7:29 am
ALZDBA (7/11/2008)
ALTER TABLE your_partitioned_table
DROP CONSTRAINT PK_ ... '_constraintname
Yes! This is what I was looking for! I just missed the drop contraint.. The primairy keys are removed so that is great! Now I have find out how I can remove the partitioning that the previous dba created.
First step we take is:
create clustered index clusid on tablename(ID);
Second step we take is:
drop index clusid on tablename with (move to [primary]);
After step one we get the message:
Msg 7726, Level 16, State 1, Line 1
Partition column 'ID' has data type varchar(36) which is different from the partition function 'part_tablename' parameter data type varchar(18).
So I would think to make part_tablename a varchar of 36... right??
But how?
July 17, 2008 at 11:42 am
Can you post :
- the tables create statement (ddl)
- the partition function ddl statement
- the partition scheme ddl statement
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 19, 2008 at 2:20 am
ALZDBA (7/17/2008)
Can you post :- the tables create statement (ddl)
- the partition function ddl statement
- the partition scheme ddl statement
Yes, I can. It seems that me and a co-worker have found the same forum 😉
The things you asked for can be found in this topic:
http://www.sqlservercentral.com/Forums/Topic536209-1291-1.aspx
Hope someone can help...
PS: the primairy key problem (used for replication) was the first of 2 problems. That one is now solved with the help in this topic, but the partitioning problem in the above mentioned topiclink is still open...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply