Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problem deleting data from a table on a linked server

Posted on 2009-07-06
5
Medium Priority
?
446 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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
Via a live example, show how to shrink a transaction log file down to a reasonable size.

650 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