Re: simple question re DELETEing in linked DB
From: gerry (germ_at_hotmail.com)
Date: 03/09/04
- Next message: Roji. P. Thomas: "Re: Cursor with dynamic SQL"
- Previous message: Greg Linwood: "Re: Aggregating by time period"
- In reply to: Steve Kass: "Re: simple question re DELETEing in linked DB"
- Next in thread: Steve Kass: "Re: simple question re DELETEing in linked DB"
- Reply: Steve Kass: "Re: simple question re DELETEing in linked DB"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 8 Mar 2004 23:53:08 -0500
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: Roji. P. Thomas: "Re: Cursor with dynamic SQL"
- Previous message: Greg Linwood: "Re: Aggregating by time period"
- In reply to: Steve Kass: "Re: simple question re DELETEing in linked DB"
- Next in thread: Steve Kass: "Re: simple question re DELETEing in linked DB"
- Reply: Steve Kass: "Re: simple question re DELETEing in linked DB"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
