Solved

Problem deleting data from a table on a linked server

Posted on 2009-07-06
5
429 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
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 7

Author Comment

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

Author Closing Comment

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

911 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

20 Experts available now in Live!

Get 1:1 Help Now