Problem deleting data from a table on a linked server

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
LVL 8
Jeff TennessenAssistant Vice PresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Atdhe NuhiuCommented:
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
momi_sabagCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff TennessenAssistant Vice PresidentAuthor Commented:
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
Jeff TennessenAssistant Vice PresidentAuthor Commented:
momi_sabag: The second suggestion worked great. Thanks so much for your help!

Jeff
0
Atdhe NuhiuCommented:
>table variable is no longer in scope

yeah that makes sense
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.