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"

Article translations Article translations
Article ID: 915074 - View products that this article applies to.
Bug #: 406684 (SQLBUDT)
Expand all | Collapse all

On This Page

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 B
ON 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: March 18, 2006 - Revision: 1.1
APPLIES TO
  • 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
Keywords: 
kbsql2005tsql kbtshoot kbexpertiseadvanced kbbug KB915074

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com