September 27, 2012 at 5:18 am
Hi,
I have a partitioned view that points to 6 DBs. Single table in each DB.
The tables in question have a check constraint on the InvoiceNr column.
Here are the constraints: (From sys.Check_Constraints)
DBName Definition
2012 ([InvoiceNr]>=(12175) AND [InvoiceNr]<=(13452))
2013 ([InvoiceNr]>=(13453) AND [InvoiceNr]<=(13453))
Current ([InvoiceNr]>(13453))
When I try to insert Invoice 13453 I get error:
Msg 4457, Level 16, State 1, Line 22
The attempted insert or update of the partitioned view failed because the value of the partitioning column does not belong to any of the partitions.
It should be inserted into DB 2013. Right?
Inserting any other invoice greater than 13453 works as expected. Inserted into the Current DB. And anything less than 13453 cannot be inserted due to PK violation, which is also expected.
I have tested this on a #Tmp table using a check constraint with the same 1 number narrow range and it works fine. So it must be something to do with the partitioned view.
I have tried recreating both the view and the check constraints to no avail.
I can insert the record directly into the table (Only in DB 2013), just not via the view.
If I recreate the check constraint using BETWEEN it automatically converts it to >= AND <=.
Any idea's?
Thanks
September 27, 2012 at 7:32 am
Can you please post the view code? The DDL for the 2013 table maybe useful as well, but I'd really like to see how the view is defined.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 27, 2012 at 8:23 am
Hi,
Thanks for responding.
The code is preety straight forward.
CREATE VIEW InvoiceData
AS
SELECT *
FROM Invoicing_IVS..InvoiceData_Actual
UNION ALL
SELECT *
FROM Invoicing_2012..IVS_InvoiceData
UNION ALL
SELECT *
FROM Invoicing_2011..IVS_InvoiceData
UNION ALL
SELECT *
FROM Invoicing_2010..IVS_InvoiceData
UNION ALL
SELECT *
FROM Invoicing_2009..IVS_InvoiceData
UNION ALL
SELECT *
FROM Invoicing_2008..IVS_InvoiceData
September 27, 2012 at 8:27 am
I have been trying to recreate this, thinking it was a bug. Looks like it isn't.
This test DOES work.
-- Create test tables
CREATE TABLE Invoicing_2004..T1 (Val Int PRIMARY KEY)
CREATE TABLE Invoicing_2005..T2 (Val Int PRIMARY KEY)
CREATE TABLE T3 (Val Int PRIMARY KEY)
-- Add some check constraints to the tables
ALTER TABLE Invoicing_2004..T1 ADD CONSTRAINT CK_T1 CHECK (Val BETWEEN 1 AND 5)
ALTER TABLE Invoicing_2005..T2 ADD CONSTRAINT CK_T2 CHECK (Val BETWEEN 6 AND 10)
ALTER TABLE T3 ADD CONSTRAINT CK_T3 CHECK (Val > 10)
-- Create a partitioned view
CREATE VIEW V1
AS
SELECT Val FROM Invoicing_2004..T1
UNION ALL
SELECT Val FROM Invoicing_2005..T2
UNION ALL
SELECT Val FROM T3
-- Enter some test data into the VIEW
INSERT INTO V1 VALUES (1)
INSERT INTO V1 VALUES (10)
INSERT INTO V1 VALUES (11)
-- Check the results
SELECT * FROM Invoicing_2004..T1
SELECT * FROM Invoicing_2005..T2
SELECT * FROM T3
SELECT * FROM V1
-- Remove the test data
TRUNCATE TABLE Invoicing_2004..T1
TRUNCATE TABLE Invoicing_2005..T2
TRUNCATE TABLE T3
-- Remove the constraints
ALTER TABLE Invoicing_2004..T1 DROP CONSTRAINT CK_T1
ALTER TABLE Invoicing_2005..T2 DROP CONSTRAINT CK_T2
ALTER TABLE T3 DROP CONSTRAINT CK_T3
-- Create new different check constraints
ALTER TABLE Invoicing_2004..T1 ADD CONSTRAINT CK_T1 CHECK (Val BETWEEN 1 AND 9)
ALTER TABLE Invoicing_2005..T2 ADD CONSTRAINT CK_T2 CHECK (Val BETWEEN 10 AND 10)
ALTER TABLE T3 ADD CONSTRAINT CK_T3 CHECK (Val > 10)
-- Enter some test data
INSERT INTO V1 VALUES (1)
INSERT INTO V1 VALUES (10) -- Should not work?!? But does in this test!
INSERT INTO V1 VALUES (11)
-- Check out the results
SELECT * FROM Invoicing_2004..T1
SELECT * FROM Invoicing_2005..T2
SELECT * FROM T3
SELECT * FROM V1
-- Take a look at the check constraints
SELECT Name, Definition FROM Invoicing_2004.sys.Check_Constraints WHERE Name LIKE 'CK_T%'
UNION
SELECT Name, Definition FROM Invoicing_2005.sys.Check_Constraints WHERE Name LIKE 'CK_T%'
UNION
SELECT Name, Definition FROM sys.Check_Constraints WHERE Name LIKE 'CK_T%'
-- Clean up
DROP VIEW V1
DROP TABLE Invoicing_2004..T1
DROP TABLE Invoicing_2005..T2
DROP TABLE T3
September 27, 2012 at 9:10 am
Thanks for posting the code.
I have no problem with this:
ALTER TABLE Invoicing_2005..T2 ADD CONSTRAINT CK_T2 CHECK (Val BETWEEN 10 AND 10)
BETWEEN is inclusive so it's the same as saying this:
ALTER TABLE Invoicing_2005..T2 ADD CONSTRAINT CK_T2 CHECK (Val = 10)
and as you showed SQL Server actually stores this:
([Val]>=(10) AND [Val]<=(10))
So now that you have a proof of concept working are you saying your issue is sorted or are you still seeing different behavior from your live VIEW? If you're still seeing an issue please post the DDL for all involved tables as well as the results from this:
SELECT SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 28, 2012 at 12:53 am
Cough, cough...... :pinch:
Just reread the whole post again. the error is in the create view.
Check constraint for invoice 13453 is in DB 2013, but that's not included in the view. :hehe:
Another of lifes little mysteries solved. :smooooth:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply