SQL Server Data Tools - Error SQL71501 Unresolved References - An Obscure Cause

Introduction

An obscure scenario led to one of the most common errors you see in SSDT development. This article explains the story.

Introduction
Problem
Reproduction of the error
Solution

Problem

For anyone using SSDT, the error

SQL71501: Procedure: [dbo].[YourProcedure] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects

is probably a familiar sight. Essentially it is telling you that there is a reference to some object in your procedure that is invalid. It might be that you are halfway through developing a solution and have not yet finished adding all the objects yet. It might be that you are reverse engineering a database into SSDT and haven’t got all the objects yet. It might just be a plain and simple broken procedure!

Take the case where you are reverse engineering an existing database, you have included all the relevant objects and you know the stored procedure is valid - but you are still seeing the error - maybe this scenario is what you have.

So the ‘problem’ is when you have a column name that has a space at the end. Obviously not a good design decision, but one that could exist

Reproduction of the error

Given a table :

CREATE TABLE [dbo].[Table1]
(
[Id] INT NOT NULL PRIMARY KEY,
[somecol ] INT NULL
)

this looks absolutely normal in designer mode :

and even in the SQL Server Object Explorer (SSOX):

But then when you define a stored procedure :

CREATE PROCEDURE [dbo].[Procedure1]

AS

select

T1.[Id],

T1.somecol

from dbo.Table1 as T1

you will see the 'squiggly lines denoting some syntax error:

and errors :

TEXT version : SQL71501: Procedure: [dbo].[Procedure1] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[Table1].[somecol] or [dbo].[Table1].[T1]::[somecol].

Solution

It could take a very long time (I know this from personal experience!) to realize that the column [somecol ] had a space as the last character.

It can be confusing as creating the stored procedure in SSMS gives no problem at all. Take both the table and procedure definition and they will successfully execute in SSMS. You can even delimit the offending column as [somecol] without the space and it still works in SSMS. (In fact this is where the stored procedure had been scripted from - maybe this a scripting issue?)

This is a discrepancy between how SSDT and SSMS works, but SSDT is being technically correct, and either SSMS is being a little lenient or SSMS handles object naming a bit differently.