I have discovered what seems, to me, like a bug in the June 2013 release of SSDT and given the problems that it created yesterday on my current gig I thought it prudent to write this blog post to inform people of it.

I’ve built a very simple SSDT project to reproduce the problem that has just two tables, [Table1] and [Table2], and also a procedure [Procedure1]:

The two tables have exactly the same definition, both a have a single column called [Id] of type integer.

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

My stored procedure simply joins the two together, orders them by the column used in the join predicate, and returns the results:

CREATE PROCEDURE [dbo].[Procedure1]
AS
    SELECT t1.*
    FROM    Table1 t1
    INNER JOIN Table2 t2
        ON    t1.Id = t2.Id
    ORDER BY Id

Now if I create those three objects manually and then execute the stored procedure, it works fine:

So we know that the code works. Unfortunately, SSDT thinks that there is an error here:

The text of that error is:

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].[Id] or [dbo].[Table2].[Id].

Its complaining that the [Id] field in the ORDER BY clause is ambiguous. Now you may well be thinking at this point “OK, just stick a table alias into the ORDER BY predicate and everything will be fine!” Well that’s true, but there’s a bigger problem here. One of the developers at my current client installed this drop of SSDT and all of a sudden all the builds started failing on his machine – he had errors left right and centre because, as it transpires, we have a fair bit of code that exhibits this scenario.  Worse, previous installations of SSDT do not flag this code as erroneous and therein lies the rub. We immediately had a mass panic where we had to run around the department to our developers (of which there are many) ensuring that none of them should upgrade their SSDT installation if they wanted to carry on being productive for the rest of the day.

Also bear in mind that as soon as a new drop of SSDT comes out then the previous version is instantly unavailable so rolling back is going to be impossible unless you have created an administrative install of SSDT for that previous version.

Just thought you should know! In the grand schema of things this isn’t a big deal as the bug can be worked around with a simple code modification but forewarned is forearmed so they say!

Last thing to say, if you want to know which version of SSDT you are running check my blog post Which version of SSDT Database Projects do I have installed?

@Jamiet