Tuesday, March 20, 2012

Could you tell whats wrong when I split table to the target partition table?

Could you tell what's wrong when I split table to the target partition table?

USE TEST

--ADD FILEGROUP---------------------
ALTER DATABASE TEST ADD FILEGROUP FG_01
ALTER DATABASE TEST ADD FILEGROUP FG_02
ALTER DATABASE TEST ADD FILEGROUP FG_03

--ADD FILE-----------------------
ALTER DATABASE TEST ADD FILE (NAME = DF_01,
FILENAME = 'D:\TEST\DF_01.ndf',
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB)
TO FILEGROUP FG_01

ALTER DATABASE TEST ADD FILE (NAME = DF_02,
FILENAME = 'D:\TEST\DF_02.ndf',
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB)
TO FILEGROUP FG_02

ALTER DATABASE TEST ADD FILE (NAME = DF_03,
FILENAME = 'D:\TEST\DF_03.ndf',
SIZE = 10MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB)
TO FILEGROUP FG_03

--CREATE PARTITION FUNCTION------------------
CREATE PARTITION FUNCTION PF_HIS_HTTP_LOG(datetime)
AS
RANGE LEFT FOR VALUES (
'20070101 23:59:59.997',
'20070102 23:59:59.997')

--CREATE PARTITION SCHEME------------------
CREATE PARTITION SCHEME PS_HIS_HTTP_LOG
AS
PARTITION PF_HIS_HTTP_LOG TO
( FG_01, FG_02, [PRIMARY])

--CREATE PARTITION TABLE ------------------
CREATE TABLE HIS_HTTP_LOG
( USERID varchar(32) ,
USERIP varchar(15) ,
USERPORT numeric(5,0) ,
OBJECTIP varchar(15) ,
OBJECTPORT numeric(5,0) ,
URL varchar(256) ,
HOST varchar(64) ,
DN varchar(64) ,
VISITIME numeric(5,0) ,
STARTIME datetime ,
ENDTIME datetime
) ON PS_HIS_HTTP_LOG(STARTIME)

--INSERT DATA,PARTITION 1 20070101---------------
DECLARE @.i int
SET @.i = 1
WHILE @.i <= 100
BEGIN
INSERT INTO HIS_HTTP_LOG VALUES(CAST(@.i AS varchar(32)),'192.168.1.1',5,'202.103.1.57',
6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070101 13:25:26.100',121),GETDATE())
SET @.i = @.i +1
END

--INSERT DATA ,PARTITION 2 20070102---------------
SET @.i = 1
WHILE @.i <= 200
BEGIN
INSERT INTO HIS_HTTP_LOG VALUES(CAST(@.i AS varchar(32)),'192.168.1.1',5,'202.103.1.57',
6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070102 11:25:26.100',121),GETDATE())
SET @.i = @.i +1
END

--CREATE A TABLE --------------------
CREATE TABLE TMP_HTTP_LOG
( USERID varchar(32) ,
USERIP varchar(15) ,
USERPORT numeric(5,0) ,
OBJECTIP varchar(15) ,
OBJECTPORT numeric(5,0) ,
URL varchar(256) ,
HOST varchar(64) ,
DN varchar(64) ,
VISITIME numeric(5,0) ,
STARTIME datetime ,
ENDTIME datetime
) ON FG_03

--INSERT DATA TO TMP_HTTP_LOG 20070103--------------
--DECLARE @.i int
SET @.i = 1
WHILE @.i <= 400
BEGIN
INSERT INTO TMP_HTTP_LOG VALUES(CAST(@.i AS varchar(32)),'192.168.1.1',5,'202.103.1.57', 6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070103 09:25:26.100',121),GETDATE())
SET @.i = @.i +1
END

--ADD CONSTRAINT---------------------
ALTER TABLE TMP_HTTP_LOG
WITH CHECK
ADD CONSTRAINT CK001
CHECK (STARTIME >= '20070103 00:00:00.000'
AND STARTIME <= '20070103 23:59:59.997')

--SPLIT RANGE ,SWITCH DATA------------------
ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED FG_03
ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070103 23:59:59.997')
ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3

--=================================================================================
Why is the error in step of"ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3"
error infomation:
message_id 4972,level 16,severity 1
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'TEST.dbo.TMP_HTTP_LOG' allows values that are not allowed by check constraints or partition function on target table 'TEST.dbo.HIS_HTTP_LOG'.

Please tell me why ? check constraints ?
Thank you very much !

The problem was handled,

"STARTIME datetime null" => STARTIME datetime not null

the hint of sql server seems not exactly

No comments:

Post a Comment