Microsoft KB Archive/286134

= PRB: Cannot Update Partitioned View After BCP or BULK INSERT into Base Table =

Article ID: 286134

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q286134



SYMPTOMS
On either a local or distributed partitioned view, if records are inserted into a base table by using the BCP utility or BULK INSERT statement without the CHECK_CONSTRAINTS option (in order to improve performance), the partitioned view becomes read-only. Subsequent attempts to perform INSERT, UPDATE, or DELETE statements on the view return the following error message:

Server: Msg 4436, Level 16, State 12, Line 1

UNION ALL view viewname is not updatable because a partitioning column was not found.



CAUSE
When you use BCP or BULK INSERT without the CHECK_CONSTRAINTS option, these two operations ignore the constaints by marking all column constraints untrusted. This invalidates the check constraint on the partitioning columns, and renders the partitioned view unable to be updated.



WORKAROUND
You can work around this problem in the following ways:   Use BCP or BULK INSERT with the CHECK_CONSTRAINTS option: bcp dbname.dbo.tblName in c:\myData.txt -c -SsvrName\instanceName -Usa -P -h&quot;CHECK_CONSTRAINTS&quot; BULK INSERT dbName.dbo.tblName FROM 'c:\myData.txt' WITH (  DATAFILETYPE = 'char',     CHECK_CONSTRAINTS )  Use the Data Transformation Services (DTS) import wizard to import data into a base table from a file. If a partitioned view becomes read-only because of a bulk load operation, you can drop and recreate the check constraint on the partitioning columns in the base table into which data was bulk loaded in order to regain the updatability of the partitioned view.



MORE INFORMATION
The following script uses a local partitioned view as an example to demonstrate the behavior, but you will see the same behavior on a distributed partitioned view: USE tempdb GO

IF EXISTS( SELECT * FROM sysobjects where name = 'myTable1' ) DROP TABLE myTable1 GO

IF EXISTS( SELECT * FROM sysobjects where name = 'myTable2' ) DROP TABLE myTable2 GO

IF EXISTS( SELECT * FROM sysobjects where name = 'myView' ) DROP VIEW myView GO

CREATE TABLE myTable1(  col1 int check( col1>=0),   col2 int,   primary key( col1 ) ) GO

CREATE TABLE myTable2(  col1 int check( col1<0),   col2 int,   primary key( col1 ), ) GO

CREATE VIEW myView AS  SELECT * FROM myTable1 UNION ALL SELECT * FROM myTable2 GO

-- Need to turn on XACT_ABORT to make partitioned view work. SET XACT_ABORT ON GO

-- Insert one row through the partitioned view. INSERT INTO myView VALUES( 1, 1 ) GO

-- BCP the record out to a text file EXEC master.dbo.xp_cmdshell 'bcp tempdb.dbo.myView out c:\myData.txt -c -Ssvrname\instanceName -Usa -P '

-- Delete the record to empty the base tables. DELETE FROM myView GO

-- Bulk load the record from the text file to a base table. EXEC master.dbo.xp_cmdshell 'bcp tempdb.dbo.myTable1 in c:\myData.txt -c -SsvrName\instanceName -Usa -P'

-- This will fail. UPDATE myView SET col1 = col1 + 1 GO

-- This will fail. INSERT INTO myView VALUES(2,2) GO

-- This will fail. DELETE FROM myView GO For more information on setting up a distributed partitioned view, see &quot;Creating a Partitioned View&quot; in SQL Server 2000 Books Online.

Keywords: kbprb KB286134

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.