THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

June 2013 release of SSDT contains a minor bug that you should be aware of

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]:

image

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:

image

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

image

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

Published Monday, July 01, 2013 8:54 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

John Paul Cook said:

My current client does a really great job at ensuring enterprise stability, best client I've ever seen at this. It's against policy to install anything without first having an archival copy safely stored in the corporate software repository. There are always able to restore or rebuild a machine to any specific state.

How about using Windows Restore to get the previous version of SSDT back?

July 1, 2013 1:20 PM
 

Kevin D said:

Hey Jaime, I'm pretty sure that existed in the previous version of SSDT as well, as I ran into it migrating a db into a project a while back.

It's good to know that the older versions are not available, I wonder if it's possible to revert back if you use the admin installation method for offline clients?

July 1, 2013 2:23 PM
 

Peter Schott said:

I don't recall this being new in the June 2013 release, but we've made it a habit to qualify everything in our statements to be on the safe side. Of course, having said that I'm sure we'd find out very quickly how many pieces of code we have that will prove me wrong. :)

Thanks for the warning, Jamie.

July 1, 2013 3:46 PM
 

jamiet said:

Hi Kevin,

That's strange because I've got the previous version installed and I'm not hitting the issue. I wonder if its some specific circumstances that lead to this happening.

July 1, 2013 3:52 PM
 

Dan Grimes said:

I definitely hit a similar issue in the previous version but with MERGE statements. I thought it was merely enforcing strict syntax practise.

July 2, 2013 8:18 AM
 

jamiet said:

Hi Dan,

Oh yeah, I've seen a lot of problems with the interpreter doing different things to what the SQL Server engine does and Merge was definitely affected (here's an example: https://connect.microsoft.com/visualstudio/feedback/details/674391/datadude-valid-merge-syntax-causes-warnings). Why I thought this issue was worth mentioning was that it was throwing errors for code that previous versions of SSDT did not - that's a rather big deal in my book.

JT

July 2, 2013 8:30 AM
 

Dan Grimes said:

Hi Jamie,

Definitely some major changes in how SSDT deals with stuff especially over Datadude.

I recently rewrote some .Net code to use the new parser library that underpins SSDT and I did notice then were some changes in how it tokenises the query and I think we are seeing things pop up with new parser that need to be finalised or addressed.

Also I have been experiencing some odd behaviour with schema compare sporadically not working properly on a large project - although I have not pinned it down yet - have you had any issues?

Also should you not now be SSDT Junkie instead? ;)

Dan

July 5, 2013 9:53 AM
 

jamiet said:

Hiya Dan,

I haven't come across any issues with Schema Compare, no. Then again, I don't use it all that much (I tend to develop in SSDT first and then publish out rather than the other way around, so Schema Compare isn't called for).

SSDT Junkie? No comment :)

JT

July 5, 2013 10:27 AM
 

lt4mosin said:

Jamie,

I can second your observations, just updated an SSDT project that has 38K objects in it to the June SSDT and was hit with the same ambigous errors all over the place. Previous versions of SSDT didn't have an issue and neither does SQL server. I was hoping that someone had a quick resolution aside from reverting to December's SSDT!

LT4

August 11, 2013 6:43 PM
 

Michael Murray said:

Any word on a resolution to this issue? I've got 1212 errors popping up in my error list and it's stopping me from Deploying to localdb. I would like to demo this software to my team but I can't if this is happening.

August 27, 2013 10:23 AM
 

jamiet said:

Hi Michael,

Haven't heard anything on a resolution I'm afraid, sorry.

JT

August 27, 2013 10:29 AM
 

Peter Schott said:

Another fun change in the June 2013 build is if you just "Build" a project, it does a partial overwrite of your pre and post deploy scripts. We're using Git so the actual project files change while those in the build output do not. If you have a post deploy script with a lot of text, then switch to a branch with a lesser amount of text, your PostDeploy.sql file is partially overwritten, leaving traces of that larger script intact.  This doesn't happen if you "Rebuild" the project, but does on a "Build". Not sure why the change, but it's hit us several times and I've switched our "deploy" batch files to use a rebuild action to be safe.

August 27, 2013 10:43 AM
 

Jill McClenahan said:

Hi everyone,

I just wanted to let you know that this is fixed in the latest release.  You can try the new version to see if it fixes your issues.  It also has the pre/post deploy script fix we did over the summer as well.

http://msdn.microsoft.com/en-us/data/hh297027

Thanks!

Jill

SSDT team - MSFT

October 11, 2013 1:13 AM
 

jamiet said:

Hi Jill,

Great news, thanks for letting us know.

Regards

Jamie

October 11, 2013 3:52 AM
 

Vaibhav Pandey said:

Although I have updated my system to SSDT 11.1.31009.1 version still the same error pops up whenever I build the solution.

November 12, 2013 3:30 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement