Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Poor error message with MERGE when source/target appear in impossible places

If I write a MERGE statement and refer to source in WHEN SOURCE NOT MATCHED
or to the target in WHEN TARGET NOT MATCHED, I get an error message. It's a
good thing in so far that it saves me from writing silly queries. Unfortunately,
the error message is the generic 'The multi-part identifier "%S" could not be bound.',
which is almost more confusing than helpful, as it makes me think that I have a
spelling error rather than this specific error.

0 votes
Sign in
(thinking…)
Password icon
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Upvotes: 1

<=-=Apr 26 2008 8:21PM=-=>

I think the error message is the correct one, By analogy, suppose you accidentally wrote

select o.OrderID
from (
select
o.OrderID,
d.Quantity
from Orders as o
join “Order Details” as d
on o.OrderID = d.OrderID
) as ord

You get the same “multi-part identifier could not be bound” error. You would get this error if you wrote SELECT oops.OrderID, too.

Erland seems to be suggesting a different error message for o.OrderID, like “Cannot refer to a table name or alias outside a derived table where it is defined”. While that could be useful, the syntax checker shouldn’t have to consider whether or not the identifier that can’t be bound happened to contain an alias [o] that appears somewhere else in the same query.

The RTM syntax for MERGE will probably reduce this kind of confusion. The syntax for the two not-matched clauses will be

WHEN NOT MATCHED [BY TARGET] — row exists in source but not in target
WHEN NOT MATCHED BY SOURCE — row exists in target but not in source

Under WHEN NOT MATCHED BY SOURCE, people will probably be less likely to mistakenly refer to the SOURCE alias.

SK

<=-=Apr 28 2008 4:09PM=-=>

Thank you for the feedback. We’ve had several comments about this, and agree that this is an area where confusion is quite likely. To help developers quickly find and correct these mistakes, 2 new error messages were recently introduced:

(5333) The identifier ‘%.*ls’ cannot be bound. Only source columns are allowed in the ‘WHEN NOT MATCHED’ clause of a MERGE statement.
(5334) The identifier ‘%.*ls’ cannot be bound. Only target columns are allowed in the ‘WHEN SOURCE NOT MATCHED’ clause of a MERGE statement.

While it’s not possible to give suggestions like this when a column name can’t be bound in the general case, this is a case where we can give specific feedback that some columns have been hidden by the binding algorithms. These should be present in the next release.

Thank you
Jerry Weiler
jweiler@microsoft.com
SQL Server Engine Development

<=-=Apr 30 2008 11:37AM=-=>

Great to see that you already have addressed this. However, you error messages use the old CTP6 syntax for MERGE. It should use the new, to appear in RC0, I believe. See
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=331948

<=-=Apr 30 2008 1:37PM=-=>

You’re correct. I only looked at the change that introduced the error messages and should have looked at the latest version. The syntax change was made after these error messages were introduced and the error messages were updated along with the syntax. The current version of 5334 is this:
The identifier ‘%.*ls’ cannot be bound. Only target columns are allowed in the ‘WHEN NOT MATCHED BY SOURCE’ clause of a MERGE statement.

Thanks

Jerry Weiler
jweiler@microsoft.com
SQL Server Engine Development

<=-=Aug 10 2008 1:03PM=-=>

The error message is the same in SQL 2008 RTM, why I have reopened this item.

<=-=Feb 4 2009 5:40PM=-=>

Hello,

Thanks for the feedback. We will look to address this issue in a future release of SQL Server as we do not change error messages in service pack releases.

Thank you and best regards,
Stephen.

0 comments

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base