BUG: Error message when you try to use the DELETE statement to delete rows from a remote table on a linked server: "Statement(s) could not be prepared" (915074)

SYMPTOMS

In Microsoft SQL Server 2005, you may receive the following error message when you try to use the DELETE statement to delete rows from a remote table that is located on a linked server:

Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'A'.

This issue occurs when all the following conditions are true:

The query involves a JOIN hint between two or more remote tables that are located on the same remote server.
The target table and all tables that are involved in the query are set to use a clustered index. For example, the table has a unique clustering index or a clustered primary key.
The target table of the DELETE statement is an alias that is defined by a table in the FROM clause.

For example, this issue occurs when you use the following query:

DELETE A FROM remote.dbname.dbo.table1 as A 
INNER JOIN remote.dbname.dbo.table2 as B ON A.column1=B.column2

WORKAROUND

To work around this issue, use one of the following methods.

Method 1: Use a WHERE clause

Use a WHERE clause in the DELETE statement to enforce a different join strategy between two tables. For example, work around this issue by using a Transact-SQL statement that is similar to the following:

DELETE FROM remote.dbname.dbo.table1 
WHERE column1 in 
(SELECT A.column1 FROM remote.dbname.dbo.table1 as A 
INNER JOIN remote.dbname.dbo.table2 as B 
ON A.column1=B.column2)

Method 2: Do not use an alias

Do not use an alias in the statement. Therefore, you must directly use the column name to reference the column in the ON clause. You can use this method if the column name that is used in the ON clause is unique in all the columns that this statement mentions.

For example, you may use the following statement: