February 25, 2011 at 6:52 am
I have a situation where I want to delete some records on a Oracle DB from a SQL Server DB via a linked server. The SQL command is called from SQL Server [ServerA] to SQL Server [ServerB] (containing the linked server to the Oracle DB). Here is the command I run:
DELETE OPENQUERY([ServerB],
'SELECT *
FROM ALLOCTEST..AAM.VIEW_DETAIL_FILTER_TB
WHERE VIEW_ID = 30');
This works when there are no records to delete. But when there are records to delete, I get the following error msg:
OLE DB provider "SQLNCLI10" for linked server "ServerB" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 16964, Level 16, State 1, Line 1
For the optimistic cursor, timestamp columns are required if the update or delete targets are remote.
Please help me solve this!
February 25, 2011 at 11:14 am
abotha-1117340 (2/25/2011)
I have a situation where I want to delete some records on a Oracle DB from a SQL Server DB via a linked server. The SQL command is called from SQL Server [ServerA] to SQL Server [ServerB] (containing the linked server to the Oracle DB). Here is the command I run:DELETE OPENQUERY([ServerB],
'SELECT *
FROM ALLOCTEST..AAM.VIEW_DETAIL_FILTER_TB
WHERE VIEW_ID = 30');
This works when there are no records to delete. But when there are records to delete, I get the following error msg:
OLE DB provider "SQLNCLI10" for linked server "ServerB" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 16964, Level 16, State 1, Line 1
For the optimistic cursor, timestamp columns are required if the update or delete targets are remote.
Please help me solve this!
Make sure there is an unique index on targetted table; also make sure to fully qualify four-part naming.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 28, 2011 at 3:55 am
Make sure there is an unique index on targetted table; also make sure to fully qualify four-part naming.
I can insert into this table using an INSERT OPENQUERY. The only problem comes in when I try to delete, I get the message above.
Please see the below definition of the targetted table. I believe there is an unique index and the four-part naming is used correctly in my call.
CREATE TABLE "AAM"."VIEW_DETAIL_FILTER_TB"
(
"VIEW_ID" NUMBER(*,0) NOT NULL ENABLE,
"VIEW_LINE_NBR" NUMBER(*,0) NOT NULL ENABLE,
"VIEW_TOKEN" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"SAVED_WITH_NAMES" VARCHAR2(1 BYTE) DEFAULT 'N',
CONSTRAINT "VIEW_DETAIL_FILTER_TB_PK" PRIMARY KEY ("VIEW_LINE_NBR", "VIEW_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "AAMINDEX" ENABLE,
CONSTRAINT "VIEW_DETAIL_FILTER_TB_FK" FOREIGN KEY ("VIEW_ID") REFERENCES "AAM"."VIEW_HEADER" ("VIEW_ID") ON
DELETE CASCADE ENABLE
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "AAMDATA" ;
CREATE INDEX "AAM"."VIEW_DETAIL_FILTER_TB_FK" ON "AAM"."VIEW_DETAIL_FILTER_TB"
(
"VIEW_ID"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS STORAGE
(
INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "AAMINDEX" ;
CREATE UNIQUE INDEX "AAM"."VIEW_DETAIL_FILTER_TB_PK" ON "AAM"."VIEW_DETAIL_FILTER_TB"
(
"VIEW_LINE_NBR", "VIEW_ID"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS STORAGE
(
INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "AAMINDEX" ;
February 28, 2011 at 7:18 am
I finally found a solution to this problem. You see it was easy to do the DELETE from the server containing the linked server to the Oracle DB. The problem was doing it from another SQL Server via the SQL Server containing the linked server to the Oracle DB.
So, instead of:
DELETE OPENQUERY([ServerB],
'SELECT *
FROM ALLOCTEST..AAM.VIEW_DETAIL_FILTER_TB
WHERE VIEW_ID = 30');
You have
EXEC [ServerB].master.dbo.sp_executesql
@stmt = N'DELETE FROM ALLOCTEST..AAM.VIEW_DETAIL_FILTER_TB WHERE VIEW_ID = 30'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply