October 7, 2004 at 7:53 am
Hello,
I have a partitioned view that I believe meets all of the pre requisites of a partitioned view as outlined by Microsoft BOL ("Partitioned Views"). I am trying to BCP into the view and am getting the followign error:
NULL
Starting copy...
SQLState = 37000, NativeError = 4437
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Partitioned view 'myView' is not updatable as the target of a bulk operation.
NULL
BCP copy in failed
NULL
If I am understanding correctly, Microsoft is saying it is possible to bcp into a partitioned view. Does anyone have any ideas why I am receiving the above error? Here is a test script to illustrate the problem:
NOTE - SQL 2000 Enterprise on Windows 2003
****************************************************
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 )
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 -SSERVER -Usa -Ppassword '
-- 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.myView in c:\myData.txt -c -SSERVER -Usa -Ppassword'
select *
from myView
*******************************************************************************
October 7, 2004 at 8:29 am
October 7, 2004 at 8:35 am
thanks for the info. I have read that KB article, however, it is specifically talking about BCPing WITHOUT the check constraints option. I have no problem using the check constraints option with BCP, but I want to BCP into a view, not into any of the base tables.
So, the refined question is, I want to BCP into a partitioned view using the check constraints option. Microsoft says this is supported, however, I cannot get it to work.
October 13, 2004 at 4:45 pm
You know... I've had this problem in the past as well... Ended up going with a work around. However, it would be good to know if there are any guru's out there who have figured this out.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply