SQL Server – Issues I Have With SSDT Based Deployments: Part 4.2

Continuing the section on trying to get a large legacy database imported into an SSDT project that actually builds, we’re at a point now where we should have genuine reference issues to clean up; all our cross database references should be sorted via a database reference to a DACPAC (with or without changing everything to use SQLCMD variables or not).

At this point, we’re going to get really annoyed with all the legacy, simply doesn’t work anyway, code left in our database over years of not tidying up.  Technical debt sucks.


Lets look at some of the things that are going to annoy us!

Genuine Reference Errors.

This is the stuff that is a genuine reference error, from legacy code, and stuff that no longer works.  However, before you get all gung-ho and delete everything that still has reference issues, you need to be aware that SSDT will mark an ambiguous reference when a column appears in an ORDER BY statement and is not aliased; despite this being valid TSQL (see below).  So we’re going to have to go through them all one by one, and either resolve the code, log bugs, mark the script to have “none” as the build action, or just delete them as appropriate.  Tables called things like “NotNeededAnymore”, “DontDropTilYouAskMike” and “aaaaaaaaaa” probably are safe to be binned!  How gung ho you want to be on that, probably depends on which DBA Category you fall into…

One piece of advice though: ensure you check in everything before you start deleting, and do the delete in a single changeset.  You can then make a note of that changeset in case any of this stuff turns out to be needed after all.

Unaliased ORDER BY columns.

I think this is a genuine problem with SSDT; this is all perfectly valid syntax in TSQL.  However, if you have a view and order without aliasing that column in either the select or the order by, and that column name exists in more than one table in your statement.

These ORDER BY references can be fixed by either properly aliasing the column name; e.g. A.Amount AS Amount, or in your ORDER BY clause using the table alias, e.g. ORDER BY A.Amount.  You shouldn’t have to, but, well you’re going to have to.

Nested Cross Database Views with SELECT *

It also seems that nested cross database references to views, e.g. a view in my database that references a view in another database, can’t be resolved if that view has a SELECT * in it.  You shouldn’t have code like that in your production database, but you’re now going to have to sort that mess out!

Warnings that objects differ due to case.

I simply don’t care about this, so you can get shut of these warnings by unticking the “Validate casing on identifiers” option in your Project settings, as per this msdn article.  Bear in mind, you’ll have to reload the project after this, which in my case, takes quite a while….

Building the project

You may find that the project will build well before this stage, but now you should have a genuinely buildable project, that we can go on to look at releasing!


3 thoughts on “SQL Server – Issues I Have With SSDT Based Deployments: Part 4.2

  1. Great post! However you forgot to mention SSDT compare does not like it’s own sqlcmd variables lol

    If you try to compare a database with an ssdt project with sqlcmd vars in it (to reference external dbs) the SSDT compare job flags this as a difference! lol

    SSDT does not even recognize their own variables! Very annoying!

    How are we supposed to run a legitimate compare of our deployed database vs the SSDT project without getting false negatives? lol

    Give it a try… this limitation is a show stopper for my company at the moment!

    Liked by 1 person

    • I haven’t actually tried that, though I’m aware if getting differences back from the publish compare and the visual studio compare!

      I’ve moved on from where I was trying to retro in this particular database; but even in a new shop with no cross db references, we still get plenty of issues with SSDT deployments to the point where I’d like to stop using them.


  2. I haven’t given up quite yet, but this was quite the left hook I must say!
    I spent hours trying to figure this out and research the problem only to find out this is expected behavior. lol
    How on earth can MS expect their software to not recognize it’s own varaibles? lol


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s