PRB: Cannot Update Partitioned View After BCP or BULK INSERT into Base Table (286134)

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.

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"CHECK_CONSTRAINTS"

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 "Creating a Partitioned View" in SQL Server 2000 Books Online.

PRB: Cannot Update Partitioned View After BCP or BULK INSERT into Base Table (286134)

SYMPTOMS

CAUSE

WORKAROUND

MORE INFORMATION