Solved

Problem deleting data from a table on a linked server

Posted on 2009-07-06
5
424 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:JTennessen
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:Philip Pinnell
Comment Utility
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
Comment Utility
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 7

Author Comment

by:JTennessen
Comment Utility
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 7

Author Closing Comment

by:JTennessen
Comment Utility
momi_sabag: The second suggestion worked great. Thanks so much for your help!

Jeff
0
 
LVL 13

Expert Comment

by:Philip Pinnell
Comment Utility
>table variable is no longer in scope

yeah that makes sense
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now