Re: simple question re DELETEing in linked DB

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Steve Kass (skass_at_drew.edu)
Date: 03/08/04 

Date: Mon, 08 Mar 2004 18:38:26 -0500

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
>
>
>
>

Relevant Pages

  • Re: simple question re DELETEing in linked DB
    ... Could not execute query against OLE DB provider 'SQLOLEDB'. ... >>If the remote table is a SQL Server table linked through the OLEDB ...
    (microsoft.public.sqlserver.programming)
  • Re: simple question re DELETEing in linked DB
    ... > actually, in this particular case, a simple remote procedure call would> suffice - I don't much care what the return value is ... ... > I hadn't had any luck using openquery to execute a remote procedure, but am> still looking into this seems as it should work. ... If you only want SQL Server>> to issue the delete query, and you don't care what happens, how many ... >> 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 ...
    (microsoft.public.sqlserver.programming)
  • Failing to connect to remote SQL Server 2000 from ASP.NET 2.0 application
    ... I have a remote server running Windows XP SP2, with both SQL Server ... provider: TCP Provider, error: 0 - A connection attempt failed because ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: MSSecure.cab
    ... SQL SP3a. ... The provider is on the SQL Server ... > Is SQL local or remote and if remote where is the provider located? ...
    (microsoft.public.sms.admin)
  • Re: TS vs VPN
    ... Using TS, w/o VPN ... The remote client connects to your local TS via Remote Desktop. ... "Foo" accesses the SQL server, which is nearby in a protected part of the network. ...
    (microsoft.public.windows.terminal_services)