January 20, 2012 at 5:48 am
CREATE TABLE [dbo].[SalesHistoryArchive](
[SaleID] [int] IDENTITY(1,1) NOT NULL,
[Product] [varchar](10) NOT NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL,
PRIMARY KEY CLUSTERED
(
[SaleID] ASC,
[Product] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF)
)
GO
and CREATE TABLE [dbo].[DIscImport](
[SaleID] [int] IDENTITY(1,1) NOT NULL,
[Product] [varchar](10) NOT NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL,
PRIMARY KEY CLUSTERED
(
[SaleID] ASC,
[Product] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF)
)
ALTER TABLE [dbo].[DIscImport] WITH CHECK ADD CONSTRAINT [ck_DIscImport] CHECK (([Product]='DPlayer'))
GO
ALTER TABLE [dbo].[DIscImport] CHECK CONSTRAINT [ck_DIscImport]
GO
CREATE PARTITION FUNCTION [pf_Product_Partition](VARCHAR(10)) AS
RANGE LEFT FOR VALUES (N'BigScreen', N'Computer', N'PoolTable')
Go
CREATE PARTITION SCHEME [ps_Product_Scheme] AS PARTITION [pf_Product_Partition]
ALL TO ([PRIMARY])
i used below queries to push second table data into first
ALTER PARTITION SCHEME ps_Product_Scheme NEXT USED [Primary]; -- allow new dadta /increase the maximum partition
ALTER PARTITION FUNCTION [pf_Product_Partition] ()
SPLIT RANGE ('DPlayer') --to add new data
ALTER TABLE DIscImport
SWITCH PARTITION 3 TO SalesHistoryArchive PARTITION 6
above query is giving below error
Msg 4972, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'DOMAIN_DBA.dbo.DIscImport' allows values that are not allowed by check constraints or partition function on target table 'DOMAIN_DBA.dbo.SalesHistoryArchive'.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 20, 2012 at 7:30 am
January 20, 2012 at 8:01 am
Is it initial setup? Or it was working fine with parttioning and you are facing issues with partition switching only.
I tried to reproduce the issue on my end & I guess two issues here.
1.First the data in partition switch is not supporting the check constraint of the table. It’s very much clear in error message.
2.I also doubt it to be an initial attempt to setup the partition. I receive following warnings when I ran your queries (removing check statements).
Warning: The specified partition 3 for the table 'dbo.DIscImport' was ignored in ALTER TABLE SWITCH statement because the table is not partitioned.
Warning: The specified partition 6 for the table 'dbo.SalesHistoryArchive' was ignored in ALTER TABLE SWITCH statement because the table is not partitioned.
January 22, 2012 at 7:20 pm
Hi Bhuvnesh,
This script demonstrates how to do what you are looking to do here:
USE tempdb
GO
-- Create partitioning function
CREATE PARTITION FUNCTION PF_Product(varchar(10))
AS RANGE LEFT
FOR VALUES ('BigScreen', 'Computer', 'PoolTable');
GO
-- Create partitioning scheme
CREATE PARTITION SCHEME PS_Product
AS PARTITION PF_Product ALL TO ([PRIMARY]);
GO
-- Create archive table
CREATE TABLE dbo.SalesHistoryArchive
(
[SaleID] integer IDENTITY(1,1) NOT NULL,
[Product] varchar(10) NOT NULL,
[SaleDate] datetime NULL,
[SalePrice] money NULL,
CONSTRAINT [PK dbo.SalesHistoryArchive (SaleID, Product) P:Product]
PRIMARY KEY CLUSTERED ([SaleID] ASC,[Product] ASC)
-- Partitioned!
ON PS_Product (Product)
);
GO
-- Sample data
INSERT dbo.SalesHistoryArchive
(Product, SaleDate, SalePrice)
VALUES
('BigScreen', '2012-01-01', $100),
('Computer', '2012-01-02', $500),
('PoolTable', '2012-01-03', $850);
GO
SELECT * FROM dbo.SalesHistoryArchive AS sha;
GO
-- Import table (not partitioned)
CREATE TABLE dbo.DiscImport
(
[SaleID] integer IDENTITY(1,1) NOT NULL,
[Product] varchar(10) NOT NULL,
[SaleDate] datetime NULL,
[SalePrice] money NULL,
CONSTRAINT [PK dbo.DiscImport (SaleID, Product)]
PRIMARY KEY CLUSTERED ([SaleID] ASC, [Product] ASC),
CONSTRAINT [CK dbo.DiscImport Product = DPlayer]
CHECK ([Product] = 'DPlayer')
);
GO
INSERT dbo.DiscImport
(Product, SaleDate, SalePrice)
VALUES
('DPlayer', '2012-01-01', $50.00),
('DPlayer', '2012-01-02', $56.00),
('DPlayer', '2012-01-03', $52.00);
-- New partition
ALTER PARTITION SCHEME PS_Product NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION PF_Product() SPLIT RANGE ('DPlayer');
GO
-- Move the whole import table into the new
-- SalesHistoryArchive partition
ALTER TABLE dbo.DiscImport
SWITCH TO
dbo.SalesHistoryArchive
PARTITION $PARTITION.PF_Product('DPlayer');
GO
-- Import table is now empty
SELECT * FROM dbo.DiscImport AS di;
-- Data is in the archive table
SELECT * FROM dbo.SalesHistoryArchive AS sha;
GO
DROP TABLE dbo.DiscImport, dbo.SalesHistoryArchive;
DROP PARTITION SCHEME PS_Product;
DROP PARTITION FUNCTION PF_Product;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 23, 2012 at 7:09 am
Thanks Paul for your quick help.
but i got one more concern.
first can below query will get replicated to another node (from server A to server B in P2P replication)? in my case it didn't
ALTER PARTITION SCHEME [ps_Product_Scheme] NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION [pf_Product_Partition]() SPLIT RANGE ('DPlayer');
GO
so my replication got failed in this case data movement on server B it went successfully on server A though.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 23, 2012 at 7:15 am
This should be helpful...
Replicating Partitioned Tables and Indexes
January 23, 2012 at 7:25 am
Dev (1/23/2012)
This should be helpful...Replicating Partitioned Tables and Indexes
Thanks Dev, that is exactly the link Bhuvnesh needs to read.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 23, 2012 at 7:35 am
SQL Kiwi (1/23/2012)
Dev (1/23/2012)
This should be helpful...Replicating Partitioned Tables and Indexes
Thanks Dev, that is exactly the link Bhuvnesh needs to read.
Just trying to help. Thanks for the appreciation Paul! 🙂
January 24, 2012 at 12:03 am
Thanks Paul and Dev.but frankly telling i did read this article earlier also but practice or implementation giving more clearance here 🙂
i got the solution to "switch partitioning option" and it worked there
but
Another issue came there showed in replication monitor
ALTER TABLE SWITCH statement failed. The specified partition 3 of target table 'CESS.dbo.SalesHistoryArchive' must be empty. (Source: MSSQLServer, Error number: 4904) i guess it happened because it can try to replicate the data (looping ) from server B to server A.Am i right ?
another thing if i know that data has been fixed but still replication monitor showing error/conflict(as above) how to avoid that ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 24, 2012 at 12:48 am
Do we also require to stop Log reader agent, Snapshot Agent and Distribution Agents for any database from where we are removing publication ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 25, 2012 at 12:19 am
Paul/Dev i am waiting here for your responses
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 25, 2012 at 1:30 am
Bhuvnesh (1/25/2012)
Paul/Dev i am waiting here for your responses
I can't answer the question but a simple comment. They responded about 2 days ago, and it's after midnight on the west coast US. My guess is they're in a different time zone and you just bumped this very recently. You'll probably need to kick back and check in tomorrow.
You might also send a polite PM to Paul and Dev asking them to check back in on this thread, to make sure it doesn't slip under the radar in the posting volume.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 25, 2012 at 11:21 am
I haven't responded because I don't know the answer. I was happy to answer the first question on this thread, but the new one is much harder to work remotely, and not something I have current experience for. Perhaps the second question is different enough that a new thread is required, so new eyes can look at it?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply