Solved

Problem deleting data from a table on a linked server

Posted on 2009-07-06
5
434 Views
Last Modified: 2012-05-07
Hello all,

I have an issue that I can't seem to resolve when trying to delete some data from a table on a linked server. The code is running from a SQL Server 2000 database, with a SQL Server 2005 server linked to it. Here is a generic version of the code which demonstrates the problem:

    DECLARE @tbl table
        (ID int PRIMARY KEY NOT NULL)
   
    INSERT INTO @tbl
    SELECT DISTINCT ID
    FROM LnkdSrv.DB1.dbo.Table1
    WHERE Field1 <> 0
   
    DELETE
    FROM LnkdSrv.DB1.dbo.Table1
    WHERE ID IN
        (SELECT ID
         FROM @tbl)

When I execute this, I get the following error message on the DELETE statement:

[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
Msg 7202, Level 11, State 2, Line 9
Could not find server 'LnkdSrv' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

However, this code works:

    DECLARE @tbl table
        (ID int PRIMARY KEY NOT NULL)
    DECLARE @ID int
   
    INSERT INTO @tbl
    SELECT DISTINCT ID
    FROM LnkdSrv.DB1.dbo.Table1
    WHERE Field1 <> 0
   
    SELECT TOP 1 @ID = ID
    FROM @tbl
   
    DELETE
    FROM LnkdSrv.DB1.dbo.Table1
    WHERE ID IN (@ID)

Because of that, I know it's not a permissions issue or a problem with the way the linked server is set up. Can anyone explain why this happens and suggest a fix or an alternative approach?

TIA,

Jeff
0
Comment
Question by:Jeff Tennessen
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 24786241
try

 DECLARE @tbl table
        (ID int PRIMARY KEY NOT NULL)
   
    INSERT INTO @tbl
    SELECT DISTINCT ID
    FROM LnkdSrv.DB1.dbo.Table1
    WHERE Field1 <> 0
   
    GO


    DELETE
    FROM LnkdSrv.DB1.dbo.Table1
    WHERE ID IN
        (SELECT ID
         FROM @tbl)
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 24786269
it is possible that table variables on the local server are not accessiable on the remote server,
anyway, why don't you just do

delete    
FROM LnkdSrv.DB1.dbo.Table1
WHERE Field1 <> 0

 ?

if you do want to do it your way, try this

with a as
(    SELECT DISTINCT ID
    FROM LnkdSrv.DB1.dbo.Table1
    WHERE Field1 <> 0
)
    DELETE t1
    FROM LnkdSrv.DB1.dbo.Table1 t1 join a t2 on t1.id = t2.id
     
0
 
LVL 8

Author Comment

by:Jeff Tennessen
ID: 24786386
andycrofts: thanks for the suggestion, but after the GO, the table variable is no longer in scope, so the DELETE still fails.

momi_sabag: Your first suggestion won't work because it's not just the records where "Field1" is non-zero that need to be deleted, but any record that has the same "ID" as a record where "Field1" is non-zero. "ID" is not actually a key on "Table1" -- I was just using generic field/table names, and I realize that this was confusing. Sorry about that. I will try your second suggestion and let you know if that solves it.

Thanks guys!

Jeff
0
 
LVL 8

Author Closing Comment

by:Jeff Tennessen
ID: 31600216
momi_sabag: The second suggestion worked great. Thanks so much for your help!

Jeff
0
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 24789505
>table variable is no longer in scope

yeah that makes sense
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question