Re: simple question re DELETEing in linked DB
From: Steve Kass (skass_at_drew.edu)
Date: 03/09/04
- Next message: Uri Dimant: "Re: simple question re DELETEing in linked DB"
- Previous message: Roji. P. Thomas: "Re: Append Query to Another Database"
- In reply to: gerry: "Re: simple question re DELETEing in linked DB"
- Next in thread: Uri Dimant: "Re: simple question re DELETEing in linked DB"
- Reply: Uri Dimant: "Re: simple question re DELETEing in linked DB"
- Reply: gerry: "Re: simple question re DELETEing in linked DB"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 09 Mar 2004 00:33:17 -0500
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: Uri Dimant: "Re: simple question re DELETEing in linked DB"
- Previous message: Roji. P. Thomas: "Re: Append Query to Another Database"
- In reply to: gerry: "Re: simple question re DELETEing in linked DB"
- Next in thread: Uri Dimant: "Re: simple question re DELETEing in linked DB"
- Reply: Uri Dimant: "Re: simple question re DELETEing in linked DB"
- Reply: gerry: "Re: simple question re DELETEing in linked DB"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
