Partitioned View - Errors after bulk inster

Partitioned View - Errors after bulk inster

• SzyKam

  Thanks, adding CHECK_CONSTRAINTS solved the problem.

  In case this problem occurs for me in a production enviroment and I need to get the optimizer to trust the check constraints again I havn't been able to get this to work. I've reviewed the link your provided and other documentation and I seem to be executing the correct commands but I am still getting the 'partitioning column was not found' error.

  I'm using the following commands to update the trust on the check constraints. Can you see anything wrong here

  ALTER TABLE [dbo].[transaction_log_20050101] WITH CHECK
  ADD CHECK ([trn_datetime] < convert(datetime,'2/1/2005'))
  GO

  ALTER TABLE [dbo].[transaction_log_20050201] WITH CHECK
  ADD CHECK ([trn_datetime] >= convert(datetime,'2/1/2005') and [trn_datetime] < convert(datetime,'3/1/2005'))
  GO

  ALTER TABLE [dbo].[transaction_log_20050301] WITH CHECK
  ADD CHECK ([trn_datetime] >= convert(datetime,'3/1/2005'))
  GO
  

  
  

• yipsi

  Or you can do:

  ALTER TABLE [dbo].[transaction_log_20050201]

  WITH CHECK CHECK CONSTRAINT <constraint_name>

  
  
  

• BenK_123

  The reason is that you performed the bulk insert without enabling constraint checking. This will result in the check constraint on the partitioning column to be untrusted. The reason being that the bulk insert process could have potentially inserted rows that do not satisfy the constraint and the optimizer has no way of knowing it without scanning all the tables. So you need to perform bulk insert with CHECK_CONSTRAINTS option also. This will of course affect throughput of the bulk insert operation. To correct the issue, you will have to renable the constraint using ALTER TABLE. As a side-note to determine such CHECK constraints, you can use the OBJECTPROPERTY meta-data function with the CnstIsNotTrusted property. Please check out the link below for an article that discusses this exact problem:

  http://www.windowsitpro.com/Article/ArticleID/46411/46411.html

  
  
  

• Bob1945

  Some additional info. I'm using SQL Server 2000 version 8.00.818 SP3 Enterprise Edition.

  The full SQL to create the tables and generate the error are the following. The last INSERT command will fail with the message 'UNION ALL view 'transaction_log' is not updatable because a partitioning column was not found.':

  CREATE TABLE [dbo].[transaction_log_20050101] (
  [merchant_id] [int] NOT NULL ,
  [id] [int] NOT NULL ,
  [trn_datetime] [datetime] NOT NULL CHECK ([trn_datetime] < cast('2/1/2005'
  as datetime)),
  [trn_value] [varchar] (32) NOT NULL)

  ALTER TABLE [dbo].[transaction_log_20050101] ADD PRIMARY KEY
  (
  [trn_datetime],
  [merchant_id],
  [id]
  )

  CREATE TABLE [dbo].[transaction_log_20050201] (
  [merchant_id] [int] NOT NULL ,
  [id] [int] NOT NULL ,
  [trn_datetime] [datetime] NOT NULL CHECK ([trn_datetime] >= cast('2/1/2005'
  as datetime) AND [trn_datetime] < cast('3/1/2005' as datetime)),
  [trn_value] [varchar] (32) NOT NULL)

  ALTER TABLE [dbo].[transaction_log_20050201] ADD PRIMARY KEY
  (
  [trn_datetime],
  [merchant_id],
  [id]
  )

  CREATE TABLE [dbo].[transaction_log_20050301] (
  [merchant_id] [int] NOT NULL ,
  [id] [int] NOT NULL ,
  [trn_datetime] [datetime] NOT NULL CHECK ([trn_datetime] >= cast('3/1/2005'
  as datetime)),[trn_value] [varchar] (32) NOT NULL)

  ALTER TABLE [dbo].[transaction_log_20050301] ADD PRIMARY KEY
  (
  [trn_datetime],
  [merchant_id],
  [id]
  )

  --DROP VIEW transaction_log
  CREATE VIEW [dbo].[transaction_log]
  AS
  SELECT * FROM [dbo].[transaction_log_20050101]
  UNION ALL
  SELECT * FROM [dbo].[transaction_log_20050201]
  UNION ALL
  SELECT * FROM [dbo].[transaction_log_20050301]

  INSERT INTO transaction_log VALUES (1,1,'1/1/2005','Value1')

  BULK INSERT NetCmsWeb..transaction_log_20050101
  FROM 'C:\temp\transactions.txt'
  WITH
  (
  CODEPAGE = 'ACP',
  FIELDTERMINATOR = '\t',
  ROWTERMINATOR = '\n'
  )

  INSERT INTO transaction_log VALUES (4,1,'1/4/2005','Value4')
  

  The contents of c:\temp\transactions.txt are:

  2 1 1/2/2005 Value2
  3 1 1/3/2005 Value3
  

  Any suggestions would be greatly apreciated, thanks!

  
  

• mantu_78

  I've found the fix to that. I need to drop the check constraints then re-create them. Seems to work that way. Thanks for the help!

  
  
• Partitioned View - Errors after bulk inster