Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 915074 - Last Review: March 18, 2006 - Revision: 1.1
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)
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
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
Microsoft has confirmed that this is a bug in the Microsoft products that are
listed in the "Applies to" section.
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
| kbsql2005tsql kbtshoot kbexpertiseadvanced kbbug KB915074 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate