You are currently offline, waiting for your internet to reconnect

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"

Bug #: 406684 (SQLBUDT)
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:
DELETE remote.dbname.dbo.table1  FROM remote.dbname.dbo.table1 INNER JOIN remote.dbname.dbo.table2 as BON column1=B.column2
STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.
Properties

Article ID: 915074 - Last Review: 03/18/2006 17:15:41 - Revision: 1.1

  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Express Edition
  • kbsql2005tsql kbtshoot kbexpertiseadvanced kbbug KB915074
Feedback