I’ve recently started to use SQL Server Data Tools (SSDT) a lot more in database development. And I must say that I’m quite impressed. Having previously used database projects in VS, and fought with msbuild, SSDT is much easier. If you are starting out with SSDT, then I can’t recommend enough that you read Jamie Thomson’s (blog | twitter) blog post series ’10 days of SSDT’ – Jamie has been a massive help during my struggle!
However even with a much better experience, you still run into issues, sometimes down to the fact that you simply are not used to working in a particular way. But sometimes, somebody somewhere has left a great big boulder for you to trip over!
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 havn’t 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 broken proc!
In my case I was reverse engineering an existing database, and I knew the proc was valid. It took me a while to figure out the cause, and hopefully by sharing it here, it can help you too.
So the ‘problem’ is when you have a column name that has a space at the end. [Remember – I came across this as I’m reverse engineering an existing database into SSDT, so I am NOT responsible for creating this table in the first place!!]
Given a table
[sql]CREATE TABLE [dbo].[Table1]
(
[Id] INT NOT NULL PRIMARY KEY,
[somecol ] INT NULL
)[/sql]
looks absolutely normal in designer mode:
and even in the SQL Server Object Explorer (SSOX):
But then when you define a proc
CREATE PROCEDURE [dbo].[Procedure1]
AS
select
T1.[Id],
T1.somecol
from dbo.Table1 as T1
you get squiggly lines:
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].
It took me a very long time to realize that the column [somecol ] had a space as the last character. The error message had me running down all kinds of dead ends. For about an hour.
The main confusion is that creating the proc in SSMS gives no problem at all. I know this as I repeatedly took both the table and procedure definition and successfully executed them 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 I had scripted the proc from, so maybe it’s a scripting issue?]
Now whilst I agree the main issue here is that some inspired individual created a column with whitespace – there is still the discrepancy between how SSDT and SSMS works.
I can see how SSDT is being technically correct, and SSMS is being a little lenient or SSMS handles object naming a bit differently.
Hope this helps
Kev