OpenQuery Delete - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

OpenQuery Delete

Author

Topic

lc6529
Starting Member

11 Posts

Posted - 2003-02-18 : 15:01:36

Configuration: Access table setup as a linked server.

I would like to be able to retrieve data from the linked server and
then delete the retrieved data from the linked server.

I can easily query the server.

Select * From OpenQuery(Giftbox,'Select * From Batch Where Serial_Number = "031511C21990002AC"')

returns a row of data.

Now, if I try the following statement

Delete from OpenQuery(GiftBox,'Select * Batch Where Serial_Number = "031511C21990002AC"')

I get the following error message:

Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. 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 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 'Microsoft.Jet.OLEDB.4.0' ICommandText::Execute returned 0x80040e21: Select * From Batch Where Serial_Number "031511C21990002AC"[PROPID=DBPROP_IRowsetChange VALUE=True STATUS=DBPROPSTATUS_CONFLICTING]].

_____

Off hand I would say the server did not like the statement...

Does anyone have any ideas?

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-18 : 15:29:17

Why don't you just migrate all of the data to SQL Server? If it's an Access application why not link Access to SQL Server? OpenQuery is also just a Pass thru to the linked server in the language of that server. I belive it takes ODBC out of the interpolation picture.

I think the following might work:

Select * from OpenQuery(GiftBox,'Delete From Batch Where Serial_Number = "031511C21990002AC"')

Hope this helps

Good Luck

Brett

8-)

PS. You have a powerfule engine in SQL Server, and a pitiful one in JET (Access), you should try and leverage what you have.

lc6529
Starting Member

11 Posts

Posted - 2003-02-19 : 13:56:23

quote:

Why don't you just migrate all of the data to SQL Server? If it's an Access application why not link Access to SQL Server? OpenQuery is also just a Pass thru to the linked server in the language of that server. I belive it takes ODBC out of the interpolation picture.

I think the following might work:

Select * from OpenQuery(GiftBox,'Delete From Batch Where Serial_Number = "031511C21990002AC"')

Hope this helps

Good Luck

Brett

8-)

PS. You have a powerfule engine in SQL Server, and a pitiful one in JET (Access), you should try and leverage what you have.

Brett, I agree with you totally about SQL vs Access but there is a very good reason the tables are in Access. The database that is being
setup as a linked server is on one of 30 machines running a custom application that allow us to monitor our MFG process. The application was purposely setup to use an Access database to insure the line could keep running in case of a network error. The engineer who designed this system will not deviate - period.

I tried running your query and received this:

Server: Msg 7357, Level 16, State 2, Line 5
Could not process object 'Delete From Batch Where Serial_Number = "031511C21990002AC"'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns. OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=Delete From CA_Batch_Detail Where Serial_Number = "031511C21990002AC"'].

lc6529
Starting Member

11 Posts

Posted - 2003-02-19 : 13:58:35

PS: Somehow when I was editing that last message I managed to add more info to the cut and paste.

The error message was:

Server: Msg 7357, Level 16, State 2, Line 5
Could not process object 'Delete From Batch Where Serial_Number = "031511C21990002AC"'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns. OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=Delete From Batch Where Serial_Number = "031511C21990002AC"'].

I had also tried the query on another table called CA_BATCH_DETAIL and received the same message.....

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-19 : 14:39:56

Can you try this?

DELETE FROM GiftBox...Batch GiftBox
Where Serial_Number = '031511C21990002AC'

I'm trying top test this on my end. I've registered the Access db, but I'm having authentication problems...it's telling it can't find the system db (or that it's open exclusively...not).

Let me know if tyhe above staement works for you though...it should.

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-19 : 14:40:51

Actually:

DELETE FROM GiftBox...Batch
Where Serial_Number = '031511C21990002AC'

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-19 : 15:34:52

OK - It's bizzaro world time...I'm getting the same message against an Access db I've used to distribute to the users. The fill in their code tables and I DTS them over. But When I do Access as a linked server I get:

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'A_tblCompany'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.

BUT:

If I move Northwind over to my server (in the same locations as the other db): ALL OF THE FOLLOWING WORKS! Microsoft MAGIC at it's best. I can't figure out what I'm not doing Right.

I'll keep looking because this is really bugging me now.

Brett

8-)

HAVE FUN WITH THIS:

EXEC sp_addlinkedserver
'NWIND',
'OLE DB Provider for Jet',
'Microsoft.Jet.OLEDB.4.0',
'D:\TAX\ETRS\NorthWind.mdb'
go

sp_addlinkedsrvlogin 'NWIND', false, 'sa', 'Admin', NULL
go

Select * from NWIND...Orders
go

Select * From OPENQUERY(NWIND,'Select * from Orders')
go

Select * Into NWIND_Orders From NWIND...Orders
go

Select Max(Order_Id) from NWIND...Orders
go

Delete From NWIND...Orders Where OrderID = (Select Max(OrderID) From NWIND...Orders)
go

Delete From NWIND...Orders
go

Insert Into NWIND...Orders (
CustomerID
, EmployeeID
, Freight
, OrderDate
, OrderID
, RequiredDate
, ShipAddress
, ShipCity
, ShipCountry
, ShipName
, ShippedDate
, ShipPostalCode
, ShipRegion
, ShipVia)
Select
CustomerID
, EmployeeID
, Freight
, OrderDate
, OrderID
, RequiredDate
, ShipAddress
, ShipCity
, ShipCountry
, ShipName
, ShippedDate
, ShipPostalCode
, ShipRegion
, ShipVia
From NWIND_Orders
Go

sp_dropserver 'NWIND', 'droplogins'
Go

lc6529
Starting Member

11 Posts

Posted - 2003-02-20 : 11:30:32

Brett..

DELETE FROM GiftBox...Batch Where Serial_Number = '031511C21990002AC'

Gave Me....

Server: Msg 7345, Level 16, State 1, Line 5
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' could not delete from table 'Batch'. There was a recoverable, provider-specific error, such as an RPC failure. [OLE/DB provider returned message: Could not delete from specified tables.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IRowsetChange::DeleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

_____

If I read your last post correctly, if the ACCESS DB is actually on the server and setup as a linked server...one can delete from it.

This would stink as this would not help in my situation....

I agree, this is maddening!

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-20 : 12:30:19

Just Curious, Can you do the delete in Access? Also is there Security Setup in Access (*.mdw or *.mdw). Also are there referential contraints on this table, ie it being a parent of another table?

Also if it's not on your server, is there a drive on the server mapped to the location, or are you using unc convention. If the server can't see the location you'll have a problem (but that doesn't seem to be your problem since the delete is failing, and you're able to execute the select).

Let me know about the above.

Brett

8-)

lc6529
Starting Member

11 Posts

Posted - 2003-02-24 : 08:26:18

Good morning Brett.

I made a copy of the ACCESS DATABASE and yes I can delete the data from within Access.

The Access database is setup to the SQL server as a UNC LINK in the LINKED SERVER properties....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources