Problem Deleting records using linked server

Question

Problem Deleting records using linked server

Kbrown

SSCommitted

Points: 1794
More actions
November 17, 2006 at 1:42 pm

#115177

Hello,
I'm attempting to utilize a linked server to delete some records. I've just started learning about linked server and ran into a problem. Three of the four statments below are successful. However, 1 Statment fails with the error message below.
Can anyone explain what the problem is?
It seems like there's a transaction against the Inventory_Specials database. But, I'm not sure how to determine this for sure or how to clean it up.
Thanks in advance for any advice!!
delete openquery(INTERFLOW02,'select * from MW.dbo.Inventory_Category where storeid=2')
delete openquery(INTERFLOW02,'select * from MW.dbo.InventoryOptions where storeid=2')
delete openquery(INTERFLOW02,'select * from MW.dbo.Inventory where storeid=2')
delete openquery(INTERFLOW02,'select * from MW.dbo.Inventory_Specials where storeid=2')
0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
Server: Msg 7345, Level 16, State 1, Line 4
OLE DB provider 'MSDASQL' could not delete from table 'select * from MW.dbo.Inventory_Specials where storeid=2'. The row has a pending delete or the deletion had been transmitted to the data source.
[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 provider returned message: Query-based delete failed because the row to delete could not be found.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange:eleteRows returned 0x80040e21: DBROWSTATUS_E_DELETED].

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 1

Does the table have a primary key? I know that I had issues with openquery when the table being deleted from did not have a primary key.

Kbrown SSCommitted Points: 1794 More actions · Answer 2

Thanks for the suggestion.

Yes, the table has a primary key. Here's the table definition:

CREATE TABLE [AquisitionSource] (

[AquisitionSourceID] [int],

[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[SortOrder] [int] NOT NULL ,

[Active] [int] NOT NULL ,

CONSTRAINT [PK_AquisitionSource] PRIMARY KEY CLUSTERED

(

[AquisitionSourceID]

&nbsp ON [PRIMARY]

) ON [PRIMARY]

GO

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

Why not use 4-part naming and a direct DELETE ?

DELETE

[INTERFLOW02].[MW].[dbo].[Inventory_Category]

WHERE storeid = 2

Kbrown SSCommitted Points: 1794 More actions · Answer 4

PW,

Thanks for your response. Yes, your suggested syntax works.

Not sure why I got on the syntax I did. When I first got started I saw it in an article on MSDN and kept with it.

Argh, makes me say to myself KISS (keep it simple stupid!).

Thanks 😉

Kim