FIX: Rows are unexpectedly deleted when you run a distributed query to delete or to update a linked server table (825043)

WORKAROUND

1. Create a unique index on the linked server table that the distributed query deletes or updates.
2. Replace the non-ANSI syntax of the DELETE Transact-SQL statement or of the UPDATE Transact-SQL statement in the distributed query with ANSI syntax.

1. Create a test database on your instance of SQL Server that is to be used as the source server.
2. Add a computer that is running SQL Server as a linked server to your instance of SQL Server.
3. On the computer that is running SQL Server and that was added as a linked server, run the following Transact-SQL script to create two tables:

   CREATE TABLE ct_batch (
   [BAT_PKEY] int NOT NULL ,
   [BTYP_KEY] int NOT NULL ,
   [BAT_START_DT] datetime NULL,
   [BAT_END_DT] datetime NULL,
   [BAT_STATUS] varchar(50) NULL,
   [BAT_DO_WORK_FLAG] int NULL)
   
   CREATE TABLE ct_batch_element ( 
   [BAT_KEY] int NOT NULL ,
   [BECC_KEY] int NOT NULL ,
   [BE_LINK_KEY] int NOT NULL ,
   [BE_CUST_STRING] varchar(20) NULL,
   [BE_CUST_VALUE] float NULL)
   GO

4. Run the following Transact-SQL script to populate the test data in the tables that you created in step 3:

   DECLARE @count int
   SET @count = 0
   WHILE @count < 100
   BEGIN
   INSERT INTO ct_batch VALUES (@count,2,GETDATE(),GETDATE(),'closed',0)
   INSERT INTO ct_batch_element VALUES (100,1,@count,null,null)
   SET @count = @count + 1
   END
   INSERT INTO ct_batch VALUES (100,2,GETDATE(),GETDATE(),'current',0)
   GO

5. On the source SQL Server database, run the following Transact-SQL script:

   DELETE Linked Server Name.pubs.dbo.ct_batch_element 
   FROM Linked Server Name.pubs.dbo.ct_batch_element A 
   JOIN Linked Server Name.pubs.dbo.ct_batch B
   ON A.bat_key=B.bat_pkey 
   WHERE A.be_link_key = 1

   Note One hundred rows may be deleted instead of only one row.

1. On the computer that is running SQL Server and that was added as a linked server, run the following Transact-SQL script to repopulate the test data that was deleted:

   DECLARE @count int
   SET @count = 0
   WHILE @count < 100
   BEGIN
   INSERT INTO ct_batch VALUES (@count,2,GETDATE(),GETDATE(),'closed',0)
   INSERT INTO ct_batch_element VALUES (100,1,@count,null,null)
   SET @count = @count + 1
   END
   INSERT INTO ct_batch VALUES (100,2,GETDATE(),GETDATE(),'current',0)
   GO

2. On the computer that is running SQL Server and that was added as a linked server, run the following Transact-SQL script:

   CREATE UNIQUE INDEX idx1 ON ct_batch_element(be_link_key)

3. On the source SQL Server database, run the following Transact-SQL script:

   DELETE Linked Server Name.pubs.dbo.ct_batch_element 
   FROM Linked Server Name.pubs.dbo.ct_batch_element A
   WHERE A.be_link_key = 1 
   AND EXISTS ( SELECT * 
   	     FROM Linked Server Name.pubs.dbo.ct_batch B 
   	     WHERE B.bat_pkey = A.bat_key )
   

   Note Only one row is deleted, as specified in the filter condition in the query.