Re: simple question re DELETEing in linked DB
From: Steve Kass (skass_at_drew.edu)
Date: 03/09/04
- Next message: Dragan Babovic: "Re: Auto alert on database changes"
- Previous message: PEACEMAKER: "searching for unicode (japanese characters)"
- In reply to: Uri Dimant: "Re: simple question re DELETEing in linked DB"
- Next in thread: gerry: "Re: simple question re DELETEing in linked DB"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 09 Mar 2004 01:55:47 -0500
Uri,
There seems to be a difference between using the 4-part name and using
OpenQuery. While I can reproduce your successful delete with the 4-part
name, when I issue
delete from OpenQuery(OtherServer,'select * from testdb.dbo.T')
where OrderID = 11000
I get the expected error message:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'SQLOLEDB'. The
provider could not support a required row lookup interface. 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' ICommandText::Execute
returned 0x80040e21: select * from
testdb.dbo.T[PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING]].
Thanks for noticing this - I'll look at the docs more carefully and see
if I'm missing something or if I should suggest an improvement!
SK
Uri Dimant wrote:
>Hi,Steve
>
>
>>If the remote table is a SQL Server table linked through the OLEDB
>>provider, it must have a unique index in order for DELETE queries to be
>>issued against it.
>>
>>
>
>I issued SELECT * INTO myTable FROM NorthWind.dbo.Orders on remote server
>which is linked server.
>So I performed DELETE command without any problem and I don't have an unique
>index on my_table
>
>DELETE FROM Server.NorthWind.dbo.myTable where OrderId=......
>
>Something wrong or I just don't understand you.
>Thanks.
>
>
>
>
>"Steve Kass" <skass@drew.edu> wrote in message
>news:#yqrigZBEHA.2796@TK2MSFTNGP09.phx.gbl...
>
>
>>Gerry,
>>
>> "Pass-through" doesn't quite mean "pretend you aren't a database
>>management system," and it's not just a question of whether the query
>>string can be executed by the remote server. There's more to a remote
>>query than just passing the query string. If you only want SQL Server
>>to issue the delete query, and you don't care what happens, how many
>>rows it returns, whether it can be rolled back or participate in
>>transactions, etc., you don't need SQL Server and an ODBC or OLE DB
>>provider - you only need a command-line query tool for the remote
>>database. You could even call one with master..xp_cmdshell if that's
>>the only kind of connectivity you need. But if you want something more,
>>your best next step is probably to look into the documentation of the
>>ODBC or OLE DB provider you use to connect to the remote server and see
>>if there are conditions under which it supports delete queries, or if it
>>can be used to execute a remote procedure, as you can do with a linked
>>SQL Server: select * from openquery(youThere, 'exec sp_who'), or select
>>* from openquery(Me, 'set fmtonly off; exec sp_helpserver')
>>
>>SK
>>
>>gerry wrote:
>>
>>
>>
>>>thanks for the response Steve.
>>>
>>>the linked db is not an SQL server db.
>>>why can we not issue a remote DELETE command the same as a remote SELECT
>>>command ?
>>>whether or not a remote DELETE can be done should be 100% dependant upon
>>>
>>>
>the
>
>
>>>remote db and have zero to do with SQL Server, that is why they call it
>>>'pass-through' isn't it ? for example with the following : DELETE FROM
>>>TAB1 WHERE F1 = 'XXX' , why would a unique index be required for this to
>>>execute on the remote server ? I want to delete all records that meet
>>>
>>>
>the
>
>
>>>specified criteria ie. F1 = 'XXX' , the remote server has no problem with
>>>this natively and pass-through is nothing other than submission of a
>>>
>>>
>command
>
>
>>>to the remote server for native processing. I understand why the unique
>>>
>>>
>key
>
>
>>>is required for DELETEs processed via SQL Server , but not by the remote
>>>server.
>>>
>>>The Books online only refer to DELETE commands for linked tables as
>>>
>>>
>executed
>
>
>>>SQL Server - this is not what I want to do, I want to execute the DELETE
>>>command on the remote server the same way that a SELECT is executed on a
>>>remote server via OPENQUERY
>>>
>>>Having said that, the remote table does have unique keys so why doesn't
>>>
>>>
>SQL
>
>
>>>Server recognize that ? how do we inform SQL Server what the key field
>>>
>>>
>is ?
>
>
>>>I would think that the remote DELETE should be called the same way as a
>>>remote procedure - but i haven't determined how to do that either.
>>>So maybe I should rephrase my original question and ask - how does one
>>>invoke a stored procedure on a remote linked database and have it execute
>>>entirely on the remote server ?
>>>
>>>gerry
>>>
>>>"Steve Kass" <skass@drew.edu> wrote in message
>>>news:%23Y6BRaWBEHA.1604@TK2MSFTNGP11.phx.gbl...
>>>
>>>
>>>
>>>
>>>>Gerry,
>>>>
>>>> If the remote table is a SQL Server table linked through the OLEDB
>>>>provider, it must have a unique index in order for DELETE queries to be
>>>>issued against it. If it is another kind of data source, it may or may
>>>>not be possible to DELETE with a remote query. Books Online has some
>>>>information in these articles:
>>>>
>>>>UPDATE and DELETE Requirements for OLE DB Providers
>>>>External Data and Transact-SQL
>>>>
>>>>SK
>>>>
>>>>gerry wrote:
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>>I am trying to delete data in a table in a linked database.
>>>>>I tried to use openquery but this gives me an error without 'any
>>>>>information' - i assume because a DELETE does not return a result set.
>>>>>Using DELETE from [lnkdb]...[tab] ... is giving me grief because
>>>>>
>>>>>
>>>>>
>>>>>
>>>'provider
>>>
>>>
>>>
>>>
>>>>>could not support a row lookup location' - i'm not sure why this is
>>>>>
>>>>>
>and
>
>
>>>>>
>>>>>
>>>i
>>>
>>>
>>>
>>>
>>>>>don't really care because i really don't want the local sql server to
>>>>>
>>>>>
>>>>>
>>>>>
>>>pull
>>>
>>>
>>>
>>>
>>>>>in the entire remote table ( 2 millin records ) to filter it and then
>>>>>
>>>>>
>>>>>
>>>>>
>>>delete
>>>
>>>
>>>
>>>
>>>>>matching records ( 4 records ).
>>>>>
>>>>>How do we execute a remote DELETE on data in a linked database ?
>>>>>I assume this would be the same as invoking a stored procedure on a
>>>>>
>>>>>
>>>>>
>>>>>
>>>linked
>>>
>>>
>>>
>>>
>>>>>database ?
>>>>>
>>>>>I am sure this must be a simple process , and I just haven't found the
>>>>>
>>>>>
>>>>>
>>>>>
>>>right
>>>
>>>
>>>
>>>
>>>>>section of the docs.
>>>>>
>>>>>anybody point me in the right direction with this
>>>>>
>>>>>thanks - gerry
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>
>>>
>>>
>>>
>
>
>
>
- Next message: Dragan Babovic: "Re: Auto alert on database changes"
- Previous message: PEACEMAKER: "searching for unicode (japanese characters)"
- In reply to: Uri Dimant: "Re: simple question re DELETEing in linked DB"
- Next in thread: gerry: "Re: simple question re DELETEing in linked DB"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
