Jeff Tennessen
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
momi_sabag: The second suggestion worked great. Thanks so much for your help!
Jeff
Jeff
>table variable is no longer in scope
yeah that makes sense
yeah that makes sense
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)