October 22, 2003 at 9:49 am
I have a partitioned view that consists of 4 tables with similar structure on server A.
It basically is like
CREATE VIEW PartView AS
SELECT * FROM TableA UNION ALL
SELECT * FROM TableB UNION ALL
SELECT * FROM TableC UNION ALL
SELECT * FROM TableD
I have a partitioned column based on date. I can insert into the view on the server
where it was created with no issues. When I try to insert from a linked server B,
Insert Into A.BaseDB.DBO.PartView
SELECT * FROM Source
I get the message:
Could not open table '"BaseDB"."dbo"."PartView"' from OLE DB provider 'SQLOLEDB'.
The provider could not support an interface required for the UPDATE/DELETE/INSERT statements.
The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available. No work was done.]
If I try to delete from the view (with 4 tables in it) it works fine.
If I modify the view to be based on only one table the inserts work fine.
What is intriguing is that if I run the insert from B, but from a table on A, like
Insert Into A.BaseDB.DBO.PartView
SELECT * FROM A.BaseDB.DBO.Source
What could be causing insert from table on B causing the failure?
thanks in advance.
October 22, 2003 at 8:11 pm
Just a guess, but try listing the actual columns rather than using the * expression. Perhaps OLE DB will not expand the select list in this circumstance.
--Jonathan
--Jonathan
October 22, 2003 at 8:58 pm
I tried. No luck.
I cannot update even rudimentary distributed partitioned views. I read the articles and BOL and I am following all the rules ( as far as I can see). What am
I missing here?
Here is the setup:
I have two servers A, and B. Server B defined as a linked server
on server A. Here is what I have on servers
SERVER B:
Has two tables and a view on these two tables in the pubs database.
CREATE TABLE [ReadTable1] (
[col1] [int] NOT NULL PRIMARY KEY CLUSTERED,
CONSTRAINT [CK_ReadTable1] CHECK ( [col1] < 100 )
)
GO
CREATE TABLE [ReadTable2] (
[col1] [int] NOT NULL PRIMARY KEY CLUSTERED,
CONSTRAINT [CK_ReadTable2] CHECK ([col1] >= 100 )
)
GO
ALTER View CurrentTable as
SELECT Col1 FROM ReadTable1
UNION ALL
SELECT Col1 FROM ReadTable2
GO
-- The partitioned view works fine on the local server, as tested by
the insert below
INSERT INTO CurrentTable (col1) values ( 1 )
SERVER A:
INSERT INTO B.Pubs.dbo.CurrentTable (col1) values ( 1 )
I get the error below:
Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"pubs"."dbo"."CurrentTable"' from OLE DB provider
'SQLOLEDB'.
The provider could not support an interface required for the
UPDATE/DELETE/INSERT statements.
The provider indicates that conflicts occurred with other properties or
requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation
generated errors.
Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IOpenRowset::OpenRowset returned 0x80040e21:
[PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK],
[PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK],
[PROPID=DBPROP_IRowsetChange VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING],
[PROPID=DBPROP_UPDATABILITY VALUE=4 STATUS=DBPROPSTATUS_OK]].
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply